highlighting specific cells

[ttowncorp]

I have a list of numbers on sheet5 and another list of numbers on sheet3 and would like to have sheet3 be colored with a specific color if any of those numbers on sheet3 match sheet5. yellow for example, how would I do that?

 

[Scott T]

On Sheet3 select the list of numbers

In Conditional Formatting Select new rule
Select Use a formula to determine which cells to format
use the formula (change ranges to match your data)

=ISNUMBER(MATCH(A1,Sheet5!$A$1:$A$9,0))

Select your formatting
click OK

 

[Vinoth_Shob]

Use the below code, change the sheet name accordingly

Function Highlighter()
Dim k As Long
Dim lrow As Long
lrow = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
lrow1 = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
For d = 2 To lrow
For k = 2 To lrow1
If Sheet2.Range("A" & k).Value = Sheet1.Range("a" & d).Value Then
Sheet2.Range("A" & k).Interior.ColorIndex = 44
End If
Next
Next


For k = 2 To lrow
For d = 2 To lrow1
If Sheet1.Range("A" & k).Value = Sheet2.Range("a" & d).Value Then
Sheet1.Range("A" & k).Interior.ColorIndex = 3
End If
Next
Next


End Function

 

[ttowncorp]

thank you for your time, but for some reason it's not highlighting anything. I highlighted the numbers on sheet3 which is D2-D225 then selected conditional formatting and changed

[=ISNUMBER(MATCH(A1,Sheet5!$A$1:$A$9,0))/CODE] to [CODE[CODE][=ISNUMBER(MATCH(V2,LISTFORMAT!$V$2:$V$402,0))/CODE]

 

[ttowncorp]

okay I think I got something wrong. the sheet I want colored is sheet7 in d2-d838 and its comparing the numbers from sheet4 in range v2-v402. but for some reason it's only highlighting only a few numbers on sheet7 and not all the numbers from the sheet4 list

 

[Vinoth_Shob]

Function Highlighter()
Dim k As Long
Dim lrow As Long
lrow = Sheet4.Range("V" & Rows.Count).End(xlUp).Row
lrow1 = Sheet7.Range("d" & Rows.Count).End(xlUp).Row
For d = 2 To lrow
For k = 2 To lrow1
If Sheet7.Range("d" & k).Value = Sheet4.Range("v" & d).Value Then
Sheet7.Range("d" & k).Interior.ColorIndex = 44
End If
Next
Next


For k = 2 To lrow
For d = 2 To lrow1
If Sheet4.Range("v" & k).Value = Sheet7.Range("d" & d).Value Then
Sheet4.Range("v" & k).Interior.ColorIndex = 3
End If
Next
Next


End Function

 

[ttowncorp]

that was it. your awesome

 

[ttowncorp]

quick question once my sheet7 get's a little bigger over time would I need to expand the "d" range in this code, or does this cover that whole column?

 

[Vinoth_Shob]

No need to change it will automatically highlight till the last column