vba Excel 2010 宏以突出显示活动单元格的行

Excel 2010 macro to highlight row of active cell

提问by MBlackburn

I'm trying to use the following macro, but it's not even recognizing it as macro so I cannot run the macro. If I change the first like to just "private/public sub test()" it will run, however then it says my Target object isn't defined.

我正在尝试使用以下宏，但它甚至无法将其识别为宏，因此我无法运行该宏。如果我将第一个更改为“private/public sub test()”，它将运行，但是它说我的 Target 对象未定义。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
' Clear the color of all the cells
Cells.Interior.ColorIndex = 0
With Target
    ' Highlight the entire row and column that contain the active cell
    .EntireRow.Interior.ColorIndex = 8
    .EntireColumn.Interior.ColorIndex = 8
End With
Application.ScreenUpdating = True
End Sub

回答by SeanC

You will have to put the macro in the code for the sheet itself, not in a separate module.

您必须将宏放在工作表本身的代码中，而不是放在单独的模块中。

What you are doing there is Eventprogramming, which has to match with what you are trying to react to.

你在那里做的是事件编程，它必须与你试图做出反应的内容相匹配。

it's not a macro in the sense you are used to. Events will react to something happening, and cannot be run normally. When you select another cell (e.g change selection from A1 to B2), the code you have reacts to the change of what cell is selected.

它不是您习惯的意义上的宏。事件会对发生的事情做出反应，无法正常运行。当您选择另一个单元格（例如将选择从 A1 更改为 B2）时，您拥有的代码会对所选单元格的更改做出反应。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
react when selection is changed
If Target.Cells.Count > 1 Then Exit Sub
If more than 1 cell is selected, then don't run the rest of the code
Application.ScreenUpdating = False
Turn off the screen, so you can't see all the changes until we're finished
' Clear the color of all the cells
Cells.Interior.ColorIndex = 0
With Target
With the cell that was selected,
' Highlight the entire row and column that contain the active cell
.EntireRow.Interior.ColorIndex = 8
.EntireColumn.Interior.ColorIndex = 8
End With
Application.ScreenUpdating = True
and now all the coloring in has been done, switch she screen back on so the results of our work has been seen.
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
选择更改时做出反应 如果选择
If Target.Cells.Count > 1 Then Exit Sub
了 1 个以上的单元格，则不要运行其余代码
Application.ScreenUpdating = False
关闭屏幕，因此在我们完成 选定的单元格之前您无法看到所有更改， 并且现在所有的着色都已完成，重新打开她的屏幕，以便看到我们工作的结果。
' Clear the color of all the cells
Cells.Interior.ColorIndex = 0
With Target

' Highlight the entire row and column that contain the active cell
.EntireRow.Interior.ColorIndex = 8
.EntireColumn.Interior.ColorIndex = 8
End With
Application.ScreenUpdating = True

End Sub

回答by Jook

Try this code in a module, call it as a macro:

在模块中尝试此代码，将其称为宏：

Public Sub Highlight()
  Application.ScreenUpdating = False
  ' Clear the color of all the cells
  ActiveSheet.Cells.Interior.ColorIndex = 0
  With ActiveCell
      ' Highlight the entire row and column that contain the active cell
      .EntireRow.Interior.ColorIndex = 8
      .EntireColumn.Interior.ColorIndex = 8
  End With
  Application.ScreenUpdating = True
End Sub

You have to use Public, so this sub becomes available in your macro menu of excel. There you can use it 'traditionally' - by wich I understand assigning a button or shortkey to it.

您必须使用Public，因此该子项在您的 excel 宏菜单中可用。在那里你可以“传统地”使用它——据我所知，为它分配一个按钮或快捷键。

回答by user3357963

This answer is based on the assumption that you want the code to run as an EVENT, after a user changes selected cell(s)

此答案基于这样的假设：在用户更改选定的单元格后，您希望代码作为 EVENT 运行

If you want this code to run in 1 worksheet only then place this in a WORKSHEET OBJECT

如果您希望此代码仅在 1 个工作表中运行，则将其放在 WORKSHEET OBJECT 中

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub

    Application.ScreenUpdating = False
    ' Clear the color of all the cells
    Target.Parent.Cells.Interior.ColorIndex = 0
    With Target
        ' Highlight the entire row and column that contain the active cell
        .EntireRow.Interior.ColorIndex = 8
        .EntireColumn.Interior.ColorIndex = 8
    End With
    Application.ScreenUpdating = True
End Sub

If you want this to run in ALL worksheets then place this in the THISWORKBOOK OBJECT (if you want to omit/include sheets you can filter on sh)

如果您希望它在所有工作表中运行，则将其放在 THISWORKBOOK OBJECT 中（如果您想省略/包含工作表，您可以在 sh 上进行过滤）

Option Explicit

选项显式

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub

    Application.ScreenUpdating = False
    ' Clear the color of all the cells
    Sh.Cells.Interior.ColorIndex = 0
    With Target
        ' Highlight the entire row and column that contain the active cell
        .EntireRow.Interior.ColorIndex = 8
        .EntireColumn.Interior.ColorIndex = 8
    End With
    Application.ScreenUpdating = True

End Sub