Report

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

Ask a question jingles9 8Posts Thursday February 27, 2014Registration date September 25, 2015 Last seen - Last answered on Mar 4, 2014 at 11:18 AM 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 .
Helpful
+1
plus moins
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
Was this answer helpful?  
jingles9 8Posts 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 2280Posts Sunday September 12, 2010Registration date ModeratorStatus October 16, 2017 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 8Posts 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 8Posts 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 2280Posts Sunday September 12, 2010Registration date ModeratorStatus October 16, 2017 Last seen - Mar 4, 2014 at 11:18 AM
You're welcome!

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!