Here is an option for you
Private Sub Worksheet_Change(ByVal Target As Range)
Dim clr, lr, lc, i, j As Long
Dim ws As Worksheet
Dim rec As Range
Set ws = Sheet2
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing Then
lr = ws.Range("A" & Rows.Count).End(xlUp).Row
lc = ws.UsedRange.SpecialCells(xlCellTypeLastCell).Column
clr = Target.Interior.Color
For i = 1 To lr
For j = 1 To lc
If Target.Value = ws.Cells(i, j).Value Then
ws.Cells(i, j).Interior.Color = clr
End If
Next j
Next i
End If
End Sub
Bookmarks