How to mark cells based on date in other cell using conditional formatting

**mharsvik** · 11-01-2012, 09:05 AM

Hei,

In my spreadsheet i have columns representing one date of the month. When weekend occur, i want that column to be formated automatically. When holiday occur, i want another formatting.

Best to see in the attachment.

Example: I know that 5. and 6. og january are a weekend. Then I want Cells F6:G26 to be marked yellow.

Example 2: I know that the 17. is a Holiday. Then I want cells R6:G26 to be marked red.

List of holidays in Start! - sheet.

example for excelforum.xlsx

Martin

**Special-K** · 11-01-2012, 12:30 PM

For holidays use this

Select cell January!B6
Use Conditional Formatting with this formula

=(DAY(B$5)=DAY(INDEX(Start!$E$5:$E$16,MATCH(TEXT($A$2,"mmmm"),Start!$C$5:$C$16,0),1)))

and format as Red
Use Format Painter to copy to other cells.