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

Solved/Closed
jingles9
Posts
8
Registration date
Thursday February 27, 2014
Status
Member
Last seen
September 25, 2015
- Feb 27, 2014 at 10:00 AM
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
- 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 .

1 reply

TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
510
Feb 27, 2014 at 11:07 AM
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
jingles9
Posts
8
Registration date
Thursday February 27, 2014
Status
Member
Last seen
September 25, 2015

Feb 27, 2014 at 12:08 PM
Hi Trowa, What sheet do I paste this in? I have tried a couple and I can't seem to get it to work. Do I need to do anything to activate it?
0
jingles9
Posts
8
Registration date
Thursday February 27, 2014
Status
Member
Last seen
September 25, 2015

Feb 28, 2014 at 09:57 AM
Hi Trowa, I tried pasting the code on the "Directory" sheet but when I run the macro nothing happens. When I paste it on the MC sheet and run the macro, it highlights all the cells red on the MC tab (not the Directory tab) regardless of whether or not it matches a value in the C column on the Directory tab. Any suggestions on what I am doing wrong? Thanks for you help.
0
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
510
Mar 3, 2014 at 10:57 AM
Hi Jingles,

The code needs to go into a module. After opening VB, go to top menu and select Insert > Module. Paste the code in the big white field.
Back at Excel hit Alt+F8 to display available macro's and double-click RunMe.

From what I understood you want to loop through Column C of the Directory sheet and compare the data with Column C of the MC sheet. If they match, change the row color on the Directory sheet.

Example:
Directory sheet, C2 = 1 and MC sheet, C2 = 2 then no match > do nothing.
Directory sheet, C3 = 3 and MC sheet, C3 = 3 then match found > Directory sheet, row 3 will be colored.

Best regards,
Trowa
0
jingles9
Posts
8
Registration date
Thursday February 27, 2014
Status
Member
Last seen
September 25, 2015

Mar 4, 2014 at 07:49 AM
Hi Trowa...Your example is correct. That is what I am trying to do. I am using Excel 2007 so the module is in a slightly different place (but I found it). I pasted in the code and then backed out, hit Alt+F8 double-clicked RunMe but nothing happens. Is there a way to upload the file to show you?
0
jingles9
Posts
8
Registration date
Thursday February 27, 2014
Status
Member
Last seen
September 25, 2015

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
0