Conditional formatting based on value in diff sheet

Hi all,

I'm looking to have conditional formatting on Sheet3 if any of the dates in Sheet1 A1:A100 match DAY in current cell...

On sheet three I have a calendar type setup with dates for each month. On the first sheet column A is used to list days absent. What I would like is for sheet three to change the color of the cell if the date is listed on sheet one. I know this is not the best program for something like this, but hoping I, with your help, can make it work.

So for example: Sheet 3, cell B5 would change to red because listed on Sheet 1.

Sheet is attached. Thanks in advance.

I have set up the conditional formatting for you for January. It has to be set up separately for each month. Also, note that your date list is 2009 and Sheet3 is dates in 2010. I added a date in 2010 to Sheet1 to test it for January 1.

Conditional formatting cannot refer to cell references on other sheets unless you create a named range. I named Sheet1 Column A as "DateList". Then for each cell on Sheet3, I determine the date by adding the day number to the date in the header and subtract 1. Then I count how many times that date appears in DateList. If it's >0, then the formatting turns red.

If you are not familiar with conditional formatting or how COUNTIF works or named ranges, let me know. The interface for conditional formatting is quite different bewteen 2003 and 2007; what version are you using?

I am using 2003, it looks like these are exactly what I was looking for. Seems so simple now!! I'll review and post back if I have any issues...thanks so much for the help!!!

JBeaucaire has a solution that improves the overall approach, by using dates in the calendars for each month. This solves the problem of having to calculate each date, and also allows you to have a single conditional formatting formula to apply to all months. Although my solution is instructive, that one is better.