跳到主要内容

浏览充满数据的大量 Excel 工作表可能具有挑战性,而且很容易忘记您的位置或误读值。 为了增强您的数据分析并减少出错的机会,我们将介绍 3 种不同的方法来动态突出显示 Excel 中选定单元格的行和列。 当您从一个单元格移动到另一个单元格时,突出显示会动态变化,提供清晰直观的视觉提示,让您专注于正确的数据,如以下演示所示:

在 Excel 中自动突出显示活动行和列


视频:在 Excel 中自动突出显示活动行和列


使用 VBA 代码自动突出显示活动行和列

要自动突出显示当前工作表中选定单元格的整个列和行,以下 VBA 代码可以帮助您完成此任务。

步骤 1:打开要自动突出显示活动行和列的工作表

步骤2:打开VBA工作表模块编辑器并复制代码

  1. 右键单击工作表名称,然后选择 查看代码 从上下文菜单中,查看屏幕截图:
  2. 在打开的 VBA 工作表模块编辑器中,将以下代码复制并粘贴到空白模块中。 看截图:
    VBA代码:自动突出显示所选单元格的行和列
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'Update by Extendoffice
        Dim rowRange As Range
        Dim colRange As Range
        Dim activeCell As Range
        Set activeCell = Target.Cells(1, 1)
        Set rowRange = Rows(activeCell.Row)
        Set colRange = Columns(activeCell.Column)
         Cells.Interior.ColorIndex = xlNone
        rowRange.Interior.Color = RGB(248, 150, 171)
        colRange.Interior.Color = RGB(173, 233, 249)
    End Sub
    
    提示:自定义代码
    • 要更改高亮颜色,您只需修改以下脚本中的 RGB 值:
      rowRange.Interior.Color = RGB(248,150,171)
      colRange.Interior.Color = RGB(173,233,249)
    • 要仅突出显示所选单元格的整行,请删除或注释掉此行(在前面添加撇号):
      colRange.Interior.Color = RGB(173, 233, 249)
    • 要仅突出显示所选单元格的整列,请删除或注释掉(在前面添加撇号)此行:
      rowRange.Interior.Color = RGB(248, 150, 171)

  3. 然后,关闭 VBA 编辑器窗口以返回到工作表。

结果:

现在,当您选择一个单元格时,该单元格的整行和整列都会自动突出显示,并且突出显示会随着所选单元格的变化而动态移动,如下所示:

笔记:
  • 此代码将清除工作表中所有单元格的背景颜色,因此,如果您有具有自定义颜色的单元格,请避免使用此解决方案。
  • 运行此代码将禁用 解开 工作表中的功能,这意味着您无法通过按 按Ctrl + Z 捷径。
  • 此代码在受保护的工作表中不起作用。
  • 要停止突出显示所选单元格的行和列,您需要删除之前添加的 VBA 代码。 之后,通过单击重置突出显示 首页 > 填色 > 没有填充.

只需单击一下 Kutools,即可自动突出显示活动行和列

面临 Excel 中 VBA 代码的限制吗? 库工具 对于Excel's 网格焦点 功能是您理想的解决方案! 它旨在解决 VBA 的缺点,提供多种突出显示样式来增强您的工作表体验。 凭借其在所有打开的工作簿中应用这些样式的能力, 库工具 确保始终高效且具有视觉吸引力的数据管理流程。

备注: 如果你想用这个 网格焦点 功能,请 下载并安装 Kutools for Excel 第一。

安装后 Kutools for Excel请点击 库工具 > 网格焦点 启用此功能。 现在,您可以看到活动单元格的行和列立即突出显示。 当您更改单元格选择时,此突出显示会动态移动。 请参阅下面的演示:

Grid Focus 功能的主要优点:
  • 保留原始单元格背景颜色:
    与 VBA 代码不同,此功能尊重工作表的现有格式。
  • 可用于受保护的板材:
    此功能在受保护的工作表中无缝运行,非常适合管理敏感或共享文档,而不会影响安全性。
  • 不影响撤消功能:
    通过此功能,您可以保留对 Excel 撤消功能的完全访问权限。 这确保您可以轻松地恢复更改,为您的数据操作增加一层安全性。
  • 大数据下性能稳定:
    此功能旨在高效处理大型数据集,即使在复杂和数据密集型电子表格中也能确保稳定的性能。
  • 多种突出显示样式:
    此功能提供了多种突出显示选项,允许您选择不同的样式和颜色,使行、列或行和列的活动单元格以最适合您的喜好和需求的方式突出显示。
提示:

使用条件格式自动突出显示活动行和列

在 Excel 中,您还可以设置条件格式以自动突出显示活动行和列。 要设置此功能,请按照下列步骤操作:

步骤一:选择数据范围

首先,选择要应用此功能的单元格范围。 这可以是整个工作表或特定数据集。 在这里,我将选择整个工作表。

第 2 步:访问条件格式

点击 首页 > 条件格式 > 新规则,请参见屏幕截图:

步骤3:设置新格式规则中的操作

  1. 新格式规则 对话框,选择 使用公式来确定要格式化的单元格 来自 选择规则类型 列表框。
  2. 格式化此公式为真的值 框中,输入这些公式之一,在本例中,我将应用第三个公式来突出显示活动行和列。
    要突出显示活动行:
    =CELL("row")=ROW()
    要突出显示活动列:
    =CELL("col")=COLUMN()
    要突出显示活动行和列:
    =OR(CELL("row")=ROW(), CELL("col")= COLUMN())
  3. 然后,单击 格式 按钮。
  4. 在下面的 单元格格式 对话框中的 选项卡,根据需要选择一种颜色来突出显示活动行和列,请参见屏幕截图:
  5. 然后,单击 OK > OK 关闭对话框。

结果:

现在,您可以看到单元格 A1 的整列和行都已突出显示。 要将此突出显示应用到另一个单元格,只需单击所需的单元格,然后按 F9 键刷新工作表,然后突出显示新选定单元格的整个列和行。

提示: 
  • 事实上,虽然 Excel 中突出显示的条件格式方法提供了一种解决方案,但它并不像使用 VBA网格焦点 特征。 此方法需要手动重新计算工作表(通过按 F9 键)。
    要启用工作表的自动重新计算,您可以将简单的 VBA 代码合并到目标工作表的代码模块中。 这将使刷新过程自动化,确保当您选择不同的单元格时突出显示立即更新,而无需按 F9 钥匙。 请右键单击工作表名称,然后选择 查看代码 从上下文菜单中。 然后将以下代码复制并粘贴到工作表模块中:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      Target.Calculate
    End Sub
    
  • 条件格式保留您手动应用于工作表的现有格式。
  • 众所周知,条件格式是不稳定的,尤其是在应用于非常大的数据集时。 它的广泛使用可能会降低工作簿的性能,影响数据处理和导航的效率。
  • CELL 函数仅在 Excel 2007 及更高版本中可用,此方法与早期版本的 Excel 不兼容。

上述方法的比较

专栏 VBA代码 条件格式 Kutools for Excel
保留单元格背景颜色 没有
支持撤消 没有
在大型数据集中稳定 没有 没有
可用于受保护的板材 没有
适用于所有打开的工作簿 仅当前工作表 仅当前工作表 所有打开的工作簿
需要手动刷新 (F9) 没有 没有

关于如何在 Excel 中突出显示所选单元格的列和行的指南到此结束。 如果您有兴趣探索更多 Excel 提示和技巧,我们的网站提供了数千个教程,请 单击此处访问它们。 感谢您的阅读,我们期待在未来为您提供更多有用的信息!


相关文章:

  • 自动突出显示活动单元格的行和列
  • 当您查看包含大量数据的大型工作表时,可能需要突出显示所选单元格的行和列,以便您可以轻松直观地读取数据,以避免误读数据。 在这里,我可以向您介绍一些有趣的技巧,以突出显示当前单元格的行和列,当单元格更改时,新单元格的列和行将自动突出显示。
  • 在 Excel 中突出显示每隔一行或每隔一列
  • 在大型工作表中,突出显示或填充每隔一行或每隔 n 行或列可提高数据可见性和可读性。 它不仅使工作表看起来更整洁,还可以帮助您更快地理解数据。 在本文中,我们将指导您通过各种方法对每隔或第 n 行或列进行着色,帮助您以更具吸引力和更直接的方式呈现数据。
  • 滚动时突出显示整个/整行
  • 如果您有一个包含多列的大型工作表,则很难区分该行上的数据。 在这种情况下,您可以突出显示活动单元格的整个行,以便在向下滚动水平滚动条时可以快速轻松地查看该行中的数据。本文将为您介绍一些解决此问题的技巧。
  • 根据下拉列表突出显示行
  • 本文将讨论如何根据下拉列表突出显示行,以以下屏幕截图为例,当我从E列的下拉列表中选择“进行中”时,我需要用红色突出显示该行。从下拉列表中选择“已完成”,我需要用蓝色突出显示该行,当我选择“未开始”时,将使用绿色突出显示该行。
Comments (61)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
And please reply to an email
This comment was minimized by the moderator on the site
Great job . But in the first task at the top of the browser page, when I did it, it was working correctly, but when I made protection for the sheet, it stopped working correctly, you see, what's the solution. Thank you
This comment was minimized by the moderator on the site
Hello,
To solve your problem, please apply the followign code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rowRange As Range
    Dim colRange As Range
    Dim activeCell As Range
    Me.Unprotect
    Cells.Interior.ColorIndex = xlNone
    Set activeCell = Target.Cells(1, 1)
    Set rowRange = Rows(activeCell.Row)
    Set colRange = Columns(activeCell.Column)
    rowRange.Interior.Color = RGB(173, 233, 249)
    colRange.Interior.Color = RGB(173, 233, 249)
    Me.Protect
End Sub



Please have a try, hope it can help you!
This comment was minimized by the moderator on the site
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    
    ' Clear previous highlighting
    Me.Cells.FormatConditions.Delete
    
    ' Check if Checkbox1 is checked
    ' If CheckBox1.Value = True Then
        ' Check if a single cell or a range of cells is selected
        If Target.Cells.Count = 1 Or Target.Cells.Count > 1 Then
            Dim selectedRange As Range
            Dim selectedRows As Range
            Dim selectedColumns As Range
            
            Set selectedRange = Target
            Set selectedRows = Me.Range(Me.Cells(selectedRange.Row, 1), Me.Cells(selectedRange.Row + selectedRange.Rows.Count - 1, 1).EntireRow)
            Set selectedColumns = Me.Range(Me.Cells(1, selectedRange.Column), Me.Cells(1, selectedRange.Column + selectedRange.Columns.Count - 1).EntireColumn)
            
            ' Add conditional formatting to highlight the selected rows
            With selectedRows.FormatConditions.Add(Type:=xlExpression, Formula1:="=TRUE")
                .Interior.Color = RGB(255, 230, 153) ' Yellow color
            End With
            
            ' Add conditional formatting to highlight the selected columns
            With selectedColumns.FormatConditions.Add(Type:=xlExpression, Formula1:="=TRUE")
                .Interior.Color = RGB(255, 230, 153) ' Yellow color
            End With
        End If
    ' End If
    
    On Error GoTo 0
End Sub
This comment was minimized by the moderator on the site
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next

' Clear previous highlighting
Me.Cells.FormatConditions.Delete

' Check if a single cell or a range of cells is selected
If Target.Cells.Count = 1 Or Target.Cells.Count > 1 Then
Dim selectedRange As Range
Dim selectedRows As Range
Dim selectedColumns As Range

Set selectedRange = Target
Set selectedRows = Me.Range(Me.Cells(selectedRange.Row, 1), Me.Cells(selectedRange.Row + selectedRange.Rows.Count - 1, 1).EntireRow)
Set selectedColumns = Me.Range(Me.Cells(1, selectedRange.Column), Me.Cells(1, selectedRange.Column + selectedRange.Columns.Count - 1).EntireColumn)

' Add conditional formatting to highlight the selected rows
With selectedRows.FormatConditions.Add(Type:=xlExpression, Formula1:="=TRUE")
.Interior.Color = RGB(255, 230, 153) ' Yellow color
End With

' Add conditional formatting to highlight the selected columns
With selectedColumns.FormatConditions.Add(Type:=xlExpression, Formula1:="=TRUE")
.Interior.Color = RGB(255, 230, 153) ' Yellow color
End With
End If

On Error GoTo 0
End Sub
This comment was minimized by the moderator on the site
can you please post code in VBA format? trying to figure out what your code looks like from what you posted in a jumble is frustrating.
This comment was minimized by the moderator on the site
Hi,

The above code is brilliant. But i have two questions and if you can help then will be much appreciated. Let me know if there is cost associated with it.

1. The above code is removing my previous formatting of the cells. Eg. if i have got the cells filld with blue then above code clears that fill.
2. Is there a code that i can use when i am extracting values from another sheet and that row in the that sheet gets highlighted as well. Eg. I want to extract vale from another cell, so when i press = sign in sheet 1 and then when i move to sheet 2 to bring the value the row in the sheet doesn't get highlighted.

Regards,
This comment was minimized by the moderator on the site
Hello, Patel
For the first question:
It may be difficult to modify the VBA code, so, I recommend you to apply the Conditional Formatting feature for solving this problem, the following article may help you:
https://www.extendoffice.com/documents/excel/3500-excel-highlight-selected-row-conditional-formatting.html

Or you can use the Kutools for Excel' Reading Layout feature, you can try it 30 days for free.
Please download from: https://www.extendoffice.com/download/kutools-for-excel.html

For the second question:
Sorry, I can't solve this problem at present,😜
This comment was minimized by the moderator on the site
How do you modify the code so that:
1. It does not permanently strip existing colors of cells. Currently, after another cell is selected, it leaves colorless previous row. I'd like to keep the colors of the worksheet, as they were before row was highlighted by this code.
2. Limit the highlight width to only Columns A thru K, not the entire row.
This comment was minimized by the moderator on the site
Hello,
It may be difficult to modify the VBA code, so, here, I recommend you to apply the Conditional Formatting feature for solving this problem, please view the below article: (Note: if you need to highlight the column A to column K, you just need to select the range you need, and then apply the conditional formatting.)
https://www.extendoffice.com/documents/excel/3500-excel-highlight-selected-row-conditional-formatting.html


Or you can use the Kutools for Excel' Reading Layout feature, you can try it 30 days for free.
Please download from: https://www.extendoffice.com/download/kutools-for-excel.html

Please try, hope it can help you!
This comment was minimized by the moderator on the site
Thank you so much for this tip!!!
Rated 5 out of 5
This comment was minimized by the moderator on the site
Another easy way to highlight your rows: https://youtu.be/iOF06mZDGIk
This comment was minimized by the moderator on the site
FAIL. Wiped out all my other cell formatting irreversibly. And doesn't work if the sheet is protected. FAIL.
This comment was minimized by the moderator on the site
Hi Gues,The code does not work in a protected worksheet. If you want to keep the original cell formatting, please try the below code.The Reading Layout feature of Kutools is stable and handy, maybe you can download a free trial to have a try.
<div data-tag="code">Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
'Update 202001106
Static xRow
Static xColumn

Dim xWs As Worksheet
Dim xCRg, xCRg1, xRRg, xRRg1, xOHRg As Range
Dim xAHRORg, xAHRORg1 As Range
Dim xAWs As Worksheet
Dim xFNum As Integer
Dim xCll As Range

On Error Resume Next

Application.ScreenUpdating = False
pRow = Selection.Row
pColumn = Selection.Column

Set xWs = ActiveWorkbook.Worksheets.Item("AutoHighlightRAndC")
Set xAWs = Target.Worksheet
If xWs Is Nothing Then
Set xWs = ActiveWorkbook.Worksheets.Add
xWs.Name = "AutoHighlightRAndC"
xWs.Visible = xlSheetHidden
xAWs.Activate
End If

If xColumn <> "" Then
Set xCRg = Columns(xColumn)
xCRg.Interior.ColorIndex = xlNone
Set xCRg = Intersect(xCRg.Worksheet.UsedRange, xCRg)
For xFNum = 1 To xCRg.Count
Set xAHRORg1 = xAWs.Cells(xFNum, xColumn)
Set xAHRORg = xWs.Cells(xFNum, xColumn)
xAHRORg1.Interior.ColorIndex = xAHRORg.Interior.ColorIndex
'xCRg.Item(xFNum).Interior.ColorIndex = xWs.Columns(xColumn).Item(xFNum).Interior.ColorIndex
Next

Set xRRg = Rows(xRow)
xRRg.Interior.ColorIndex = xlNone
Set xRRg = Intersect(xCRg.Worksheet.UsedRange, xCRg)
For xFNum = 1 To xRRg.Count
Set xAHRORg1 = xAWs.Cells(xRow, xFNum)
Set xAHRORg = xWs.Cells(xRow, xFNum)
xAHRORg1.Interior.ColorIndex = xAHRORg.Interior.ColorIndex
'xRRg.Item(xFNum).Interior.ColorIndex = xWs.Rows(xRow).Item(xFNum).Interior.ColorIndex
Next

End If

xRow = pRow
xColumn = pColumn

Set xCRg = Columns(pColumn)
Set xCRg1 = Intersect(xCRg.Worksheet.UsedRange, xCRg)
For xFNum = 1 To xCRg1.Count
Set xOHRg = xWs.Cells(xFNum, pColumn)
xOHRg.Interior.ColorIndex = xCRg1.Item(xFNum).Interior.ColorIndex
Next

Set xRRg = Rows(pRow)
Set xRRg1 = Intersect(xRRg.Worksheet.UsedRange, xRRg)

For xFNum = 1 To xRRg1.Count
Set xCll = xWs.Cells(pRow, xFNum)
xCll.Interior.ColorIndex = xRRg1.Item(xFNum).Interior.ColorIndex
Next
xCRg.Interior.ColorIndex = 34
xRRg.Interior.ColorIndex = 34
Application.ScreenUpdating = True
End Sub
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations