如何将突出显示活动选定单元格的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 = 22
        .Pattern = xlSolid
    End With
    With Rows(pRow).Interior
        .ColorIndex = 6
        .Pattern = xlSolid
    End With
End Sub

转换失败的尝试:

Sub Highlighter()
'
' Highlighter Macro
'
' Keyboard Shortcut: Ctrl+Shift+H
'
Dim xSheet As Worksheet
For Each xSheet In This.Workbook.Worksheets
    xSheet.Select
    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 = 22
        .Pattern = xlSolid
    End With
    With Rows(pRow).Interior
        .ColorIndex = 6
        .Pattern = xlSolid

Next xSheet
End Sub

请帮忙!谢谢!

第三次尝试>>它正在工作，但如何在工作簿中进行选择更改以应用于所有工作表?

Sub Highlighter()
'
' Highlighter Macro
'
' Keyboard Shortcut: Ctrl+Shift+H
'

Dim xSheet As Worksheet
For Each xSheet In ActiveWorkbook.Worksheets

xSheet.Select
Static xRow
Static xColumn
If xColumn <> "" Then
    With xSheet.Columns(xColumn).Interior
        .ColorIndex = xlNone
    End With
    With xSheet.Rows(xRow).Interior
        .ColorIndex = xlNone
    End With
End If
pRow = Selection.Row
pColumn = Selection.Column
xRow = pRow
xColumn = pColumn
With xSheet.Columns(pColumn).Interior
    .ColorIndex = 22
    .Pattern = xlSolid
End With
With xSheet.Rows(pRow).Interior
    .ColorIndex = 6
    .Pattern = xlSolid
End With

您的代码可以很好地用于任何一个工作表，如果它位于该工作表的工作表代码区域

如果您希望代码在多个工作表上工作，则必须将其放置在每个工作表的工作表代码区域。

循环不行

Replace

This.Workbook.Worksheets

ThisWorkbook.Worksheets

对象This在vba中不存在。然而，有一个全局属性叫做ThisWorkbook(一个词)。

然而，你的代码有另一个问题，那就是静态变量的使用。您现在所做的方式将只保存最后一个工作表的选择。您将需要找到另一种方式来存储该信息。一种需要最小代码更改的方法是使用dictionaries，这将允许您存储多个值并将它们与特定的工作表关联。

粘贴到模块中:

Sub Highlight()
Dim xSheet As Worksheet
For Each xSheet In Worksheets
    xSheet.Select
    With xSheet.Cells.Interior
        .ColorIndex = xlNone
    End With
    With xSheet.Columns(Selection.Column).Interior
        .ColorIndex = 22
        .Pattern = xlSolid
    End With
    With xSheet.Rows(Selection.Row).Interior
        .ColorIndex = 6
        .Pattern = xlSolid
    End With

Next xSheet
End Sub

粘贴到每个工作表的代码中:

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Call Highlight
End Sub

如果要通过代码向工作表的Worksheet_SelectionChange事件添加代码，请参见以下问题:

将以下内容放入模块:

Sub Highlight(ws As Worksheet, xRow As Long, xColumn As Long)
    'Clear previous formatting
    If xColumn > 0 Then
        With ws.Columns(xColumn).Interior
            .ColorIndex = xlNone
        End With
        With ws.Rows(xRow).Interior
            .ColorIndex = xlNone
        End With
    End If
    pRow = Selection.Row
    pColumn = Selection.Column
    xRow = pRow
    xColumn = pColumn
    With ws.Columns(pColumn).Interior
        .ColorIndex = 22
        .Pattern = xlSolid
    End With
    With ws.Rows(pRow).Interior
        .ColorIndex = 6
        .Pattern = xlSolid
    End With
End Sub

并将以下内容放入每个工作表中:

Private xRow As Long
Private xColumn As Long
Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Call Highlight(Me, xRow, xColumn)
End Sub

然后每个工作表将跟踪特定于该工作表的"先前选择"，并且这些值将传递给公共Highlight子例程。

一个警告:当工作簿被保存时，高亮部分也将被保存。重新打开工作簿时，宏将不再知道"以前的选择"。您可以在Workbook_Open事件中添加一些内容来设置每个工作表的初始xRow和xColumn值。

相关内容

最新更新

热门标签：