提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

如何在Excel中自动突出显示活动单元格的行和列?

doc突出显示row1

当您查看包含大量数据的大型工作表时,可能需要突出显示所选单元格的行和列,以便您可以轻松直观地读取数据以避免误读。 在这里,我可以介绍一些有趣的技巧来突出显示当前单元格的行和列,当单元格更改时,新单元格的列和行自动突出显示如下屏幕截图:


使用VBA代码自动突出显示所选单元格的行和列

单击以突出显示所选单元格的行和列


在Excel中单击以突出显示所选单元格的行和列:

Kutools for Excel's 阅读布局 实用程序可帮助您快速突出显示Excel中所选单元格的行和列。 看截图:

Kutools for Excel:比200方便的Excel加载项,可以在60天免费试用。 立即下载免费试用版!

Office选项卡在Office中启用选项卡式编辑和浏览,使您的工作更轻松......
Kutools for Excel - 最佳办公生产力工具将解决您的大部分Excel问题
  • 重用任何东西: 将最常用或最复杂的公式,图表和其他任何内容添加到您的收藏夹中,并在将来快速重复使用它们。
  • 超过20文本功能: 从文本字符串中提取数字; 提取或删除部分文本; 将数字和货币转换为英语单词...
  • 合并工具:多个工作簿和表格合二为一; 合并多个单元格/行/列而不丢失数据; 合并重复行和总和...
  • 拆分工具:根据价值将数据拆分为多个表格; 一个工作簿到多个Excel,PDF或CSV文件; 一列到多列......
  • 粘贴跳过 隐藏/过滤行; 数和总和 按背景颜色; 创建邮件列表和 通过Cell的价值发送电子邮件...
  • 超级过滤器: 创建高级过滤方案并应用于任何工作表; 排序 按周,日,频率等; 筛选 通过大胆,公式,评论......
  • 超过300强大的功能; 适用于Office 2007-2019和365; 支持所有语言; 在公司轻松部署; 全功能60天免费试用。

使用VBA代码自动突出显示所选单元格的行和列

以下VBA代码可以帮助您自动突出显示工作表中当前单元格的整个列和行,请执行以下步骤:

1。 打开工作表,您将自动突出显示活动单元格的行和列,右键单击工作表选项卡并选择 查看代码 从上下文菜单。

2。 然后将以下VBA代码复制并粘贴到空白代码窗口中:

VBA代码:自动高亮所选单元格的行和列

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'Update 20140318
Static xRow
Static xColumn
If xColumn <> "" Then
    With Columns(xColumn).Interior
        .ColorIndex = xlNone
    End With
    With Rows(xRow).Interior
        .ColorIndex = xlNone
    End With
End If
pRow = Selection.Row
pColumn = Selection.Column
xRow = pRow
xColumn = pColumn
With Columns(pColumn).Interior
    .ColorIndex = 6
    .Pattern = xlSolid
End With
With Rows(pRow).Interior
    .ColorIndex = 6
    .Pattern = xlSolid
End With
End Sub

文档阅读,layout3

3。 然后按 其他 + Q 键合在一起返回工作表,现在当您选择单元格时,此单元格的整个行和列已突出显示。 它将随着所选单元格的变化而动态移动。

文档阅读,layout4

备注:

1。 在上面的代码中,你可以改变 .ColorIndex = 6 颜色到你喜欢的其他颜色。

2。 此VBA代码仅适用于您在上述步骤2中选择的工作表。

3。 如果您不想再次突出显示所选单元格的行和列,则需要删除上面的VBA,并通过单击设置为不填充所选行和列 主页 > 填色 > 没有填充.

4。 如果您的工作表中有一些彩色单元格,单击该单元格后颜色将会丢失,然后移动到其他单元格。


使用Kutools for Excel自动突出显示所选单元格的行和列

如果要突出显示单元格区域的整个行和列,则代码将不可用,但使用 Kutools for Excel阅读布局 查看,你可以很快解决这个问题。

Kutools for Excel :与超过300方便的Excel加载项, 免费试用60天无限制.

1。 在工作表中选择一个单元格或一系列单元格。 点击 Kutools > 阅读布局 激活此功能。 所选单元格的所有列和行都立即高亮显示。 它会随着所选单元的变化而动态移动。 查看截图:

小技巧:你可以为此设置一些属性 阅读布局 功能在 阅读布局设置 对话框,请点击 Kutools > 阅读 > 阅读布局设置 打开 阅读布局设置 对话框。

doc突出显示row1

您只能通过选择高亮显示所选单元格的行 直线 选项​​:

doc突出显示row1

您只能通过选择高亮显示所选单元格的列 垂线 选项​​:

doc突出显示row1

备注:

1。 您可以通过取消选中取消该实用程序 阅读布局阅读 下拉列表。

2。 此 阅读版式视图 功能应用于工作簿中的所有工作表。

3。 此 阅读版式视图 功能将在下次启动工作簿时被禁用。


Office Tab - 在Excel中选项卡式浏览,编辑和管理工作簿:

Office选项卡将Web浏览器(如Google Chrome,Internet Explorer新版本和Firefox)中显示的选项卡界面带到Microsoft Excel。 它将是一个节省时间的工具,在您的工作中无可比拟。 见下面的演示:

点击免费试用Office Tab!

Excel的Office选项卡


单击以突出显示所选单元格的行和列

Kutools for Excel 包含了比300更方便的Excel工具。 免费试用60天无限制。 立即下载免费试用!



Kutools for Excel - 最佳办公生产力工具提高80%的生产力

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能60天免费试用。
kte tab 201905

Office选项卡为Office提供选项卡式界面,使您的工作更轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Shaikh Wasim · 6 months ago
    When i try the above code to highlight row & column, its nt working, also i tried another code which worked as i found it in below comments which is

    Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

    Cells.Interior.ColorIndex = 0
    Target.EntireRow.Interior.ColorIndex = 22
    Target.EntireColumn.Interior.ColorIndex = 22

    it worked but cleared all my cell colors to white & highlight the active cell, Please help me on this @crystal
    • To post as a guest, your comment is unpublished.
      crystal · 6 months ago
      Hi Shaikh Wasim,
      The VBA code in the post works well in my case. Which Excel version do you use? As the "color cleared" problem you memtioned, I can't help to solve it yet. Way not try the Reading Layout utility of Kutools for Excel? It won't make any changes to your worksheet. Thanks for commenting.
  • To post as a guest, your comment is unpublished.
    Alok Ghosh · 8 months ago
    After pasting the VBA code I saved as Add-In but not working. I want to change the color of the intersection cell also...
    • To post as a guest, your comment is unpublished.
      crystal · 7 months ago
      Good Day,
      You can change the highlight color by changing the .ColorIndex value. But the color of the intersection cell can't be changed alone.
  • To post as a guest, your comment is unpublished.
    Urska · 1 years ago
    Above VBA code did not work. I've tried the below code for highlighting both the selected column and row. It's shorter and easier



    Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

    Cells.Interior.ColorIndex = 0
    Target.EntireRow.Interior.ColorIndex = 22
    Target.EntireColumn.Interior.ColorIndex = 22


    End Sub
  • To post as a guest, your comment is unpublished.
    D · 1 years ago
    How can I change the VBA so the text color changes when the row is highlighted?



    Also, if you want to just highlight a row without the column.. Here is the code changes.



    Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    'Update 20140318 'Updated By dlsodders for
    'Select Just Row
    Static xRow

    If xRow <> "" Then
    With Rows(xRow).Interior
    .ColorIndex = xlNone
    End With
    End If
    pRow = Selection.Row

    xRow = pRow

    With Rows(pRow).Interior
    .ColorIndex = 10
    .Pattern = xlSolid
    End With
    End Sub
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good Day,
      The following code can help you change the text color when the row is highlighted.

      Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
      Static xRow
      Static xCol
      Dim pRow As Long
      Dim pCol As Long
      If Target.Count = 1 Then
      If xRow <> "" Then
      With Rows(xRow)
      .Interior.ColorIndex = xlNone
      .Font.ColorIndex = xlAutomatic
      End With
      End If
      pRow = Selection.Row
      pCol = Selection.Font.ColorIndex
      xRow = pRow
      xCol = pCol
      With Rows(pRow)
      .Interior.ColorIndex = 10
      .Interior.Pattern = xlSolid
      .Font.Color = -16776961
      End With
      End If
      End Sub
  • To post as a guest, your comment is unpublished.
    David · 1 years ago
    Thank you for posting this! It's very useful in check reconciliation via spreadsheet.
  • To post as a guest, your comment is unpublished.
    Jay · 1 years ago
    The VBA to highlight a single cell works but this one does not.
  • To post as a guest, your comment is unpublished.
    nolan · 1 years ago
    how do you prevent the undo & redo buttons from being deleted with this macro not using the Kutools
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good Day,
      Sorry about that. The undo & redo problem in the macro can't be solved.
  • To post as a guest, your comment is unpublished.
    David · 2 years ago
    I changed your code from highlighting the interior to making red borders.
    .BorderAround Weight:=xlThick, Color:=vbRed
    I select a cell, the borders turns red. Great. I copy, select another cell, those borders turn red, but the clipboard clears. I cannot figure out how to use borders, and keep the copy/paste functionality.
    Any suggestions?
  • To post as a guest, your comment is unpublished.
    Chienph · 2 years ago
    Hi. How can i high light entire row if one of cell is merge. I see kutool can make a line across a merge cell. Pl help me this solution
  • To post as a guest, your comment is unpublished.
    Vicente · 3 years ago
    Great article!. Life saver for me.
    And if you want the actual cell to be highlighted in yellow and row/cells in grey, this is the code:

    Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    'Update 20151216
    Static xRow
    Static xColumn
    If xColumn "" Then
    With Columns(xColumn).Interior
    .ColorIndex = xlNone
    End With
    With Rows(xRow).Interior
    .ColorIndex = xlNone
    End With
    End If
    pRow = Selection.Row
    pColumn = Selection.Column
    xRow = pRow
    xColumn = pColumn
    With Columns(pColumn).Interior
    .ColorIndex = 15
    .Pattern = xlSolid
    End With
    With Rows(pRow).Interior
    .ColorIndex = 15
    .Pattern = xlSolid
    End With
    Selection.Interior.ColorIndex = 6
    End Sub
    • To post as a guest, your comment is unpublished.
      Kamran · 3 years ago
      Correct code as follows:

      Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
      'Update 20151216
      Static xRow
      Static xColumn
      If xColumn "" Then
      With Columns(xColumn).Interior
      .ColorIndex = xlNone
      End With
      With Rows(xRow).Interior
      .ColorIndex = xlNone
      End With
      End If
      pRow = Selection.Row
      pColumn = Selection.Column
      xRow = pRow
      xColumn = pColumn
      With Columns(pColumn).Interior
      .ColorIndex = 15
      .Pattern = xlSolid
      End With
      With Rows(pRow).Interior
      .ColorIndex = 15
      .Pattern = xlSolid
      End With
      Selection.Interior.ColorIndex = 6
      End Sub


      [quote name="Vicente"]Great article!. Life saver for me.
      And if you want the actual cell to be highlighted in yellow and row/cells in grey, this is the code:

      Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
      'Update 20151216
      Static xRow
      Static xColumn
      If xColumn "" Then
      With Columns(xColumn).Interior
      .ColorIndex = xlNone
      End With
      With Rows(xRow).Interior
      .ColorIndex = xlNone
      End With
      End If
      pRow = Selection.Row
      pColumn = Selection.Column
      xRow = pRow
      xColumn = pColumn
      With Columns(pColumn).Interior
      .ColorIndex = 15
      .Pattern = xlSolid
      End With
      With Rows(pRow).Interior
      .ColorIndex = 15
      .Pattern = xlSolid
      End With
      Selection.Interior.ColorIndex = 6
      End Sub[/quote]
  • To post as a guest, your comment is unpublished.
    KSV · 4 years ago
    HOW CAN I MAKE IT POSSIBLE IF HAVE SHEET THAT IS ALREADY COLORED.
  • To post as a guest, your comment is unpublished.
    mike_thomas · 4 years ago
    This works if you don't have colors not in conditional formatting. Just add two more with loops.

    Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    'Update 20140318
    Static xRow
    Static xColumn
    If xColumn "" Then
    With Rows(xRow).Interior
    .ColorIndex = xlNone
    End With
    End If
    pRow = Selection.Row
    xRow = pRow
    With Rows(pRow).Interior
    .ColorIndex = 6
    .Pattern = xlSolid
    End With
    With Rows(pRow - 1).Interior
    .ColorIndex = 0
    .Pattern = x1Solid
    End With
    With Rows(pRow + 1).Interior
    .ColorIndex = 0
    .Pattern = x1Solid
    End With
    End Sub
  • To post as a guest, your comment is unpublished.
    SaltWater · 4 years ago
    When I reopen my workbook highlighted column from the prior session remains - and does change as I move around the worksheet in a new session. Is there a way to "clear" the prior session each time the workbook is opened?
  • To post as a guest, your comment is unpublished.
    Aditya · 4 years ago
    Bravo..exactly what I was looking for.....thanks a lot!!
  • To post as a guest, your comment is unpublished.
    listermoss · 4 years ago
    This is the code I used for just the row (grey highlight):

    Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

    'Update 20140318
    Static xRow
    If xRow "" Then
    With Rows(xRow).Interior
    .ColorIndex = xlNone
    End With
    End If
    pRow = Selection.Row
    xRow = pRow
    With Rows(pRow).Interior
    .ColorIndex = 15
    .Pattern = xlSolid
    End With


    End Sub
    • To post as a guest, your comment is unpublished.
      Christina · 2 years ago
      Copying and pasting your code did not work properly, but after fixing the syntax errors it worked amazingly! Thank you.
      Here is the working code for highlighting (yellow) the row only:

      Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

      'Update 20140318
      Static xRow
      If xRow "" Then
      With Rows(xRow).Interior
      .ColorIndex = xlNone
      End With
      End If
      pRow = Selection.Row
      xRow = pRow
      With Rows(pRow).Interior
      .ColorIndex = 6
      .Pattern = xlSolid
      End With
      End Sub
  • To post as a guest, your comment is unpublished.
    betakos · 4 years ago
    how can i stop it if i want it to stop??
  • To post as a guest, your comment is unpublished.
    Ton Hermes · 4 years ago
    How can I change the colors of the bars?
    Thnaks for this great solution.

    Regards, Ton
  • To post as a guest, your comment is unpublished.
    Mac · 4 years ago
    Any plans to add Mac functionality for Kutools?
  • To post as a guest, your comment is unpublished.
    Charles · 4 years ago
    To keep your cell color you will have to create a Conditional Formatting for the cells.
  • To post as a guest, your comment is unpublished.
    Will · 4 years ago
    Just what I was looking for. Thank you!
  • To post as a guest, your comment is unpublished.
    vipe · 5 years ago
    This works brilliantly. Is there a way of making it not show up on printing?
  • To post as a guest, your comment is unpublished.
    Matthewwwww · 5 years ago
    Hi! I really like this code but like Suresh said, it removes original cell colors! How can I keep my original cell colors?? Please respond, thanks!
  • To post as a guest, your comment is unpublished.
    felix · 5 years ago
    Hi, after putting in the code, the "undo" function no longer works. Can someone help?
  • To post as a guest, your comment is unpublished.
    Suresh Singh · 5 years ago
    Hi,

    the given code works fine for highlighting the row and coloumn of active cell. but the original cell colour along with coloumn and rows colour settings are lost. How to overcome this?
    • To post as a guest, your comment is unpublished.
      Janet D · 3 years ago
      This option is now available to non-coders in 2010 as long as you have the Developer tab.

      Under Developer, click Properties.

      Set EnableOutlining to True.

      Limitation:
      This appears to only apply to one worksheet at a time, although easy to set for other worksheets by clicking on tabs at bottom (while leaving the Properties dialog box open).

      Why Excel does not make this available under Options, well, it is Microsoft.
    • To post as a guest, your comment is unpublished.
      betakos · 4 years ago
      i use insert table to my data and it does not change original colouring
    • To post as a guest, your comment is unpublished.
      RaveFromDave · 4 years ago
      Here's my version for highlighting the current row (from A to AK). To avoid wiping out cell colouring I use a red border instead...plus it restores my preferred border to the row just left.

      Private Sub Worksheet_SelectionChange(ByVal Target As Range)

      Dim lRow As Long, lCol As Long
      Static lPrevRow As Long

      'Restore previous active row to...light blue dotted line
      If lPrevRow > 0 Then
      Range("A" & lPrevRow, "AK" & lPrevRow).BorderAround Weight:=xlHairline, Color:=RGB(162, 200, 255)
      End If

      'Highlight current row with a red border
      lRow = Target.Row
      Range("A" & lRow, "AK" & lRow).BorderAround Weight:=xlThin, Color:=RGB(255, 0, 0)
      lPrevRow = lRow

      End Sub
      • To post as a guest, your comment is unpublished.
        Randy · 2 years ago
        Could not get this to work - please help, thanks :-)
        • To post as a guest, your comment is unpublished.
          Bozo · 2 years ago
          "Please help" - do you expect an answer with such a vague request, and no effort to explain the problem?
      • To post as a guest, your comment is unpublished.
        MARIO · 3 years ago
        HI I HAVE THE SAME PROBLEM WITH SURESH
        CAN YOU PLEASE PROVIDE ME WITH THE WHOLE AMENDED CODE FOR NOT WIPING THE ORIGINAL CELL COLOUR

        I'M USING THIS:

        Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
        'Update 20140318
        Static xRow
        Static xColumn
        If xColumn "" Then
        With Columns(xColumn).Interior
        .ColorIndex = xlNone
        End With
        With Rows(xRow).Interior
        .ColorIndex = xlNone
        End With
        End If
        pRow = Selection.Row
        pColumn = Selection.Column
        xRow = pRow
        xColumn = pColumn
        With Columns(pColumn).Interior
        .ColorIndex = 6
        .Pattern = xlSolid
        End With
        With Rows(pRow).Interior
        .ColorIndex = 6
        .Pattern = xlSolid
        End With
        End Sub
      • To post as a guest, your comment is unpublished.
        calixco · 3 years ago
        you are a geniuos

        now, how abot rows and columsn?
      • To post as a guest, your comment is unpublished.
        farfan · 4 years ago
        Oh! man, works like a charm!!! great