Report

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

Ask a question jingles9 8Posts Thursday February 27, 2014Registration date September 25, 2015 Last seen - Last answered on Mar 4, 2014 11:18AM by TrowaD
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 
Helpful
+1
moins plus
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
jingles9 8Posts Thursday February 27, 2014Registration date September 25, 2015 Last seen - Mar 4, 2014 09:05AM
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
Reply
TrowaD 2112Posts Sunday September 12, 2010Registration date ModeratorStatus February 27, 2017 Last seen - Mar 4, 2014 10:50AM
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
Reply
jingles9 8Posts Thursday February 27, 2014Registration date September 25, 2015 Last seen - Mar 4, 2014 11:03AM
You are amazing...it works :) Is there a way to make a button the user can press to run this code?
Reply
jingles9 8Posts Thursday February 27, 2014Registration date September 25, 2015 Last seen - Mar 4, 2014 11:08AM
Hi Trowa, I just figured out how to add the button. Thanks again for all your help. It is greatly appreciated!
Reply
TrowaD 2112Posts Sunday September 12, 2010Registration date ModeratorStatus February 27, 2017 Last seen - Mar 4, 2014 11:18AM
You're welcome!
Reply
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!