CODEWITHSUNDEEP
excelvba
itzy
itzy

Reputation: 11755

Excel macro to highlight all cells that match value in current cell

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.

Any ideas?

Upvotes: 1

Views: 22338

Answers (2)

datatoo
datatoo

Reputation: 2049

@Reafidy provided a good macro and this will do the same with conditional formatting

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

Upvotes: 3

Reafidy
Reafidy

Reputation: 8441

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

Upvotes: 3

Related Questions