Solved

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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
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…

911 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now