?
Solved

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

Posted on 2011-03-16
4
Medium Priority
?
182 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 7

Accepted Solution

by:
hippohood earned 1000 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 1000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

770 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