excel - 如何保持初始单元格颜色,而代码为hilighting活动行



Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
'toggles worksheet colors
'code will remove all color
'and color active row and column

 If ActiveCell.Interior.ColorIndex <> xlNone Then
 Cells.Interior.ColorIndex = xlNone
 Cells.Interior.ColorIndex = xlNone
 ActiveCell.EntireRow.Interior.ColorIndex = 4
End If
End Sub

如果行有初始颜色,它将被删除。 让我知道活动行如何突出显示,通过更改行,将获得初始颜色?

时间: 作者:

见鬼我找不到add但是我为你重新创建了密码。 请注意,这没有经过彻底测试。 不管我做了什么小测试它都能正常工作。


  1. 创建隐藏图纸。
  2. 将当前单元格的当前格式存储在隐藏表的第 1行
  3. 将当前选定的行编号存储在隐藏工作表的单元格 A2
  4. 当移动到另一行时,检索最后一行编号并恢复。



enter image description here

Private Sub Workbook_Open()
 Dim ws As Worksheet

 '~~> Delete the Temp sheet we created i.e if we created
 Application.DisplayAlerts = False
 On Error Resume Next
 On Error GoTo 0
 Application.DisplayAlerts = True

 '~~> ReCreate the Sheet
 Set ws = ThisWorkbook.Sheets.Add
 '~~> i am using a normal name. Chnage as applicable
 ws.Name ="MyHiddenSheet"
 '~~> Hide the sheet
 ws.Visible = xlSheetVeryHidden
End Sub

在相关工作表代码区域中。 我使用 Sheet1 作为例子

enter image description here

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 '~~> Don't do anything if multiple cells are selected
 If Target.Cells.CountLarge> 1 Then Exit Sub

 Dim ws As Worksheet

 '~~> Set our relevant sheet
 Set ws = ThisWorkbook.Sheets("MyHiddenSheet")

 '~~> Get the row number of the last row we had selected earlier
 '~~> For obvious reasons, this will be empty for the first use.
 If Len(Trim(ws.Cells(2, 1).Value)) <> 0 Then
 '~~> If user has moved to another row then
 '~~> Restor the old row
 If Target.Row <> Val(ws.Cells(2, 1).Value) Then
 Rows(ws.Cells(2, 1).Value).PasteSpecial xlFormats
 End If
 End If

 '~~> Copy the current row's format to the hidden sheet
 ws.Rows(1).PasteSpecial xlFormats
 '~~> Store the current rows value in cell A2
 ws.Cells(2, 1).Value = Target.Row

 '~~> Highlight the current row in a shade of blue.
 '~~> Chnage as applicable
 With Rows(Target.Row).Interior
. Pattern = xlSolid
. PatternColorIndex = xlAutomatic
. ThemeColor = xlThemeColorAccent5
. TintAndShade = 0.799981688894314
. PatternTintAndShade = 0
 End With

 '~~> Remove the `Ants` which appear after you do a copy
 Application.CutCopyMode = False
End Sub


enter image description here



定义工作表级别 NAME"rownum"并指定值 0.

使用公式 =(ROW()=ROWNUM) 添加条件格式,并添加要用于行突出显示的任何格式。

你的SelectionChange sub是:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Me.Names("ROWNUM").RefersToR1C1 ="=" & Target.Cells(1).Row
End Sub