Conditional Formatting Formula Issue

I'm hoping someone may have dealt with something similiar as this. I've searched this site and the intrawebs, but have not found anything exactly for what I'm trying to do. I'm actually not sure if it can be done!

Background: The folks I work for do performance improvement visits to our 200+ hospitals, so they like to get a sense of how the hospitals were performing before AND after they conducted their visit.

Purpose: Develop a worksheet the displays the performance for over 20 hospital metrics for 200+ hospitals. The sheet I have attached is not the actual one due to the sensitive information contained within it. Instead I have widdled down to generic identifiers and only one metric (Volume) just to demostrate what I'm trying to accomplish. If you imagine, each metric is shown on a separate worksheet, and so there will be around 20 worksheets. Each worksheet will contain a row for each hospital.

Issue: The directors will enter the Throughput Date on the 'Site Listing' worksheet. That will propegate to each of the metric sheets automatically, so they just do this all in one place. On the actual metric sheet, I'm trying to code a formula that will highlight each cell denoting when the visit occured for that hospital. (Note: The Throughput Date column will be hidden once I get this figured out).

In my head, I know what I want to do...just not sure how to code it. I'd like to check if the Column Title (Jan2012, Feb2012, Mar2012, etc.) matches the value in column C (Throughput Date). If it does, highlight the cell....if not, just leave it alone. I can get this to work for a row, but not a formula that will work for all rows and columns. I hate to manually have to create a separate conditional formatting formula for each row.

Below is what I'm trying to get the sheet to do:

MasterSheetImg.gif

In the attached file, I've played around with the conditional formula for Row2 (Hospital A), but if I extend in down to include Row 8 in 'Applies To', all cells are then highlighted. I've played around with different absolute references, but can't seem to get what I want. Normally I can sleep on it and figure out the next day, but not this time!

Any advice would be welcomed and appreciated!!! Thanks in advance.

Change the formula to
=$C2=D$1

AND

Change the 'Applies To' Range to
=$D$2:$AM$8

Thanks SOOOO much. You saved me from any further damage to my wall (and head). Now that I look at what you provided it makes sense. For some reason I just couldn't grasp it.

Thanks again!

Brian

You're welcome.