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

jingles9 8 Posts Thursday February 27, 2014Registration date September 25, 2015 Last seen - Feb 27, 2014 at 10:00 AM - Latest reply: TrowaD 2385 Posts Sunday September 12, 2010Registration dateModeratorStatus July 5, 2018 Last seen
- 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 

10 replies

Best answer
TrowaD 2385 Posts Sunday September 12, 2010Registration dateModeratorStatus July 5, 2018 Last seen - Feb 27, 2014 at 11:07 AM
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

Thank you, TrowaD 1

Something to say? Add comment

CCM has helped 1757 users this month

jingles9 8 Posts Thursday February 27, 2014Registration date September 25, 2015 Last seen - Mar 4, 2014 at 09:05 AM
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 2385 Posts Sunday September 12, 2010Registration dateModeratorStatus July 5, 2018 Last seen - Mar 4, 2014 at 10:50 AM
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 8 Posts Thursday February 27, 2014Registration date September 25, 2015 Last seen - Mar 4, 2014 at 11:03 AM
You are amazing...it works :) Is there a way to make a button the user can press to run this code?
jingles9 8 Posts Thursday February 27, 2014Registration date September 25, 2015 Last seen - Mar 4, 2014 at 11:08 AM
Hi Trowa, I just figured out how to add the button. Thanks again for all your help. It is greatly appreciated!
TrowaD 2385 Posts Sunday September 12, 2010Registration dateModeratorStatus July 5, 2018 Last seen - Mar 4, 2014 at 11:18 AM
You're welcome!