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 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 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 .

Highlight row if cell contains text google sheets
Google meet download for pc - Download - Video calls
If cell contains date then return value ✓ - Office Software Forum
Google sheet right to left - Guide
Google authenticator pc - Download - Password management
Highlight cell if another cell contains text ✓ - Excel Forum

1 response

Answer 1 / 1

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
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

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?

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.

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
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

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?

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

Similar discussions

How to use conditional format based on text?

Counting cells containing only numbers

Excel - IF function with multiple words

Highlight cell if another cell contains specific text

If a cell contains any text then add a value

Similar discussions

Subscribe To Our Newsletter!