Solved

Highlight Row Where Cell Value is same as Cell Value from another Worksheet

Posted on 2011-03-16
4
177 Views
Last Modified: 2012-05-11
Two sheets Master Census and Canceled Contracts

If the exact patient name is entered into the Patient Name column on the Canceled Contracts sheet I want the entire row of the same Patient Name to turn red on the Master Census sheet.

I got the cell to change using the Match Function but I want the entire row to change to red fill.
0
Comment
Question by:Chris12090
  • 2
4 Comments
 
LVL 7

Accepted Solution

by:
hippohood earned 250 total points
ID: 35153070
Use Conditional Formatting

1) First, I would recommend to assigned a named range to list of patients on Master Census sheet: Select the range with the names (column?) and type in the Name Box (the one to the left from Function field) somehting like "CensusPatientsList"

2) Select all the table in the Cancelled Contracts
3) Go to Home\Conditional Formatting\New Rule
4) Choose Use formula to determine...
5) type in the formula from below (it assumes that your names are in column A starting from row 2)
6) press the format button and select tab Fill - choose red color, press OK, OK

See the attached example workbook. Let me know if you need help conditional-formatting.xlsx
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 250 total points
ID: 35153092
Hello Chris,

Assuming you define a named range for the Patient Name column in Cancelled contracts, e.g. name is PN then in Master Census sheet I assume you have Patient names in A2 down so highlight the whole range of data, e.g. A2:Z100 and use this formula in conditional formatting

=MATCH($A2,PN,0)

That might be similar to what you used already but the key requirements are that you slect the whole range, including all columns....and that you use the $ in front of A2....

regards, barry
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 35153101
Apologies hippohood - I didn't see your reply when I posted......our methods are essentially the same except you used COUNTIF and I used MATCH - both are valid

regards, barry
0
 

Author Closing Comment

by:Chris12090
ID: 35153496
Thank you both for your help!  You both were right, however, for my situation Barry's solution was easiest.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
conditional formatting 4 43
VBA Help 18 45
Excel Cell Total 3 22
Excel VBA Find Lowest Row number in any selection 8 26
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question