vba Excel 宏以突出显示与当前单元格中的值匹配的所有单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6460458/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel macro to highlight all cells that match value in current cell
提问by itzy
I'm looking for a macro that will automatically highlight any cells in the current worksheet if the value of those cells is the same as the currently-selected cell. So if cell B3 is currently selected, and it contains the value 3, then all other cells with a value of 3 will be highlighted.
我正在寻找一个宏，如果这些单元格的值与当前选择的单元格相同，它将自动突出显示当前工作表中的任何单元格。因此，如果当前选择了单元格 B3，并且它包含值 3，那么所有其他值为 3 的单元格将被突出显示。
Any ideas?
有任何想法吗？
回答by datatoo
@Reafidy provided a good macro and this will do the same with conditional formatting
@Reafidy 提供了一个很好的宏，这将与条件格式相同
Sub HighLightCells()
ActiveSheet.UsedRange.Cells.FormatConditions.Delete
ActiveSheet.UsedRange.Cells.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:=ActiveCell
ActiveSheet.UsedRange.Cells.FormatConditions(1).Interior.ColorIndex = 4
End Sub
Put this in the sheet selection change event
把它放在工作表选择更改事件中
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
call HighLightCells
End Sub
回答by Reafidy
Use conditional formatting.
使用条件格式。
If you really need a macro then:
如果你真的需要一个宏，那么：
Sub HighlightCells()
Dim rCell As Range
If ActiveCell.Value = vbNullString Then Exit Sub
Set rCell = ActiveCell
Do
Set rCell = ActiveSheet.UsedRange.Cells.Find(ActiveCell.Value, rCell)
If rCell.Address <> ActiveCell.Address Then
rCell.Interior.Color = 65535
Else
Exit Do
End If
Loop
End Sub
