Solved

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

Posted on 2011-03-16
4
180 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 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

739 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