Highlight row on one sheet if a number matches on another sheet

[Solved/Closed]
Report
Posts
8
Registration date
Thursday February 27, 2014
Status
Member
Last seen
September 25, 2015
-
Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
-
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 .

1 reply

Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
481
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
1
Thank you

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

CCM 2942 users have said thank you to us this month

Posts
8
Registration date
Thursday February 27, 2014
Status
Member
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
Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
481
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
Posts
8
Registration date
Thursday February 27, 2014
Status
Member
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?
Posts
8
Registration date
Thursday February 27, 2014
Status
Member
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!
Posts
2805
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 14, 2021
481
You're welcome!