تفضل اخي ربما هدا ما تقصده
Sub ChangeColor()
Dim lrow&
Dim WS1 As Worksheet: Set WS1 = Sheets("Raw Data")
Dim WS2 As Worksheet: Set WS2 = Sheets("Do Not Include")
lrow = WS1.Range("B" & Rows.Count).End(xlUp).Row
Rng = WorksheetFunction.CountA(WS1.Range("A4", WS1.Range("A4").End(xlDown))) + 3
Application.ScreenUpdating = False
WS1.Activate
WS1.Range(Cells(5, 2), Cells(Rng, 2)).Interior.ColorIndex = 0
If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
Set r = WS1.Range("B5:B" & lrow)
For Each cell In r
If cell.Value = WS2.Range("A1") Then
cell.Interior.Color = RGB(34, 153, 166)
cell.Select
End If
Next
Application.ScreenUpdating = True
End Sub
test 7.xlsm