Highlight row on one sheet if a number matches on another sheet [Solved/Closed]

Posts
8
Registration date
Thursday February 27, 2014
Last seen
September 25, 2015
- - Latest reply: TrowaD
Posts
2441
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 18, 2018
- Mar 4, 2014 at 11:18 AM
Hello,
I have a spreadsheet with multiple tabs. I am trying to figure out how to get a row to highlight on the tab called "Directory" if a number in column C matches a number in column C on another tab labeled "MC". Any assistance would be greatly appreciated .
See more 

1 reply

Best answer
Posts
2441
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 18, 2018
400
1
Thank you
Hi Jingles9,

Assuming row 1 is used for header and that the data in column C has already been entered, try this code:
Sub RunMe()
Dim lRow As Integer

Sheets("Directory").Activate
lRow = Range("C" & Rows.Count).End(xlUp).Row

For Each cell In Range("C2:C" & lRow)
If cell.Value = Sheets("MC").Cells(cell.Row, "C") Then
Rows(cell.Row).Interior.ColorIndex = 3
End If
Next cell

End Sub

The highlighted row is colored red (ColorIndex = 3). If you wish to change color, then record a macro, applying your color, stop recording, check which ColorIndex Excel has given your color.

Best regards,
Trowa

Say "Thank you" 1

A few words of thanks would be greatly appreciated. Add comment

CCM 4478 users have said thank you to us this month

jingles9
Posts
8
Registration date
Thursday February 27, 2014
Last seen
September 25, 2015
-
Sorry Trowa, I just realized your example was not correct. I want it to search all of column C on Directory sheet and if any of the numbers in that column match any of the numbers in Column C on the MC sheet then I want it to highlight red.

Directory sheet, C3=3 and MC sheet, C8=3 then match found >Directory sheet, row 3 will be coloured
Directory sheer, C4=8 and MC sheet column C does not have 8 in it, then no match >do nothing
TrowaD
Posts
2441
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 18, 2018
400 -
Hi Jingles,

In that case use the following code:
Sub RunMe()
Dim lRow1, lRow2, x As Integer

lRow2 = Sheets("MC").Range("C" & Rows.Count).End(xlUp).Row
Sheets("Directory").Activate
lRow1 = Range("C" & Rows.Count).End(xlUp).Row
x = 1

For Each cell In Range("C2:C" & lRow1)
Do
x = x + 1
If cell.Value = Sheets("MC").Cells(x, "C") Then
Rows(cell.Row).Interior.ColorIndex = 3
End If
Loop Until x = lRow2
x = 1
Next cell

End Sub

Best regards,
Trowa
jingles9
Posts
8
Registration date
Thursday February 27, 2014
Last seen
September 25, 2015
-
You are amazing...it works :) Is there a way to make a button the user can press to run this code?
jingles9
Posts
8
Registration date
Thursday February 27, 2014
Last seen
September 25, 2015
-
Hi Trowa, I just figured out how to add the button. Thanks again for all your help. It is greatly appreciated!
TrowaD
Posts
2441
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 18, 2018
400 -
You're welcome!