Solved

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

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

679 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