Process to Highlight Entire Row if one field matches # on a single column of a dif sheet

[deeproots79]

I have an Excel Book with the columns
Customer--Meter#--LastReading--Comments and so on.
That worksheet has a 2nd sheet with just Meter# (of those meters to be worked differently)
On the first sheet I need to highlight the entire row if the meter # matches any meter # on the 2nd sheet and Insert the text "Retro-Fit" into the comments field.

Any idea on how to best achieve that?

[TMS]

You can use COUNTIF to determine if the number exists in the second sheet. So you can use that in the Comments columns and also in Conditional Formatting.

Suggest you post a sample workbook.

[deeproots79]

I will give that a try.

[TMS]

See the attached sample workbook. Note there is a Named Range to refer to Column A on Sheet 2

[deeproots79]

excel1image.png

I can't get it to highlight. Please take a look at the formula I am using.

[TMS]

My eyesight isn't that good. Please post a workbook, not a picture. Or look at my example.

[deeproots79]

I tried to post the workbook and follow your example. I 'll try a different browser. The popup for inserting a file won't work.

[deeproots79]

WEEK 8 (72) PAPERWORK.xlsx
Did this upload?

[TMS]

Try this ... I cleared the cell fill colour (and changed the formula to use a Named Range)

[deeproots79]

Looks Great. Thanks. I should just be able to copy it going forward.

[TMS]

You're welcome.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[deeproots79]

I need to add 1 more rule to highlight a Second group a different color.
I attached the spreadsheet and you will that I modified the criteria in the comments box to insert some other text if another event existed.
Basically it checks the SERVICE ADDRESS for the text "*SPLK*" which indicates a Sprinkler meter.
I need to highlight those rows orange.
What would the formula in the conditional formatting be for this case?WEEK 8 (72) PAPERWORK.xlsx

[deeproots79]

Wrong File. I modified the one you gave me!WEEK 8 (72) PAPERWORK TMS.xlsx

[deeproots79]

I figured it out copying what you did with the other formula.