Conditional Format based on dates

[BaseballFan]

I have a work schedule that is 365 columns wide - all very narrow so that 2
or 3 months fits onto the screen at a time. Row 1 is the month name, with
each month a "merged and centered" cell spanning the 30 or 31 cells for that
month. Row 2 is the "day of month" number (1,2,3...30,31,1,2,3...etc). Row
3 through whatever (currently 22) is for the employee... each cell indicates
what the employee is doing that day (on duty, on vacation, etc.).

I'd like to format rows 2 and below so that the current day is highlighted
yellow. So, on Jan 20, T2:T22 would be yellow. Today (April 5th) would have
CQ2:CQ22 highlighted.

I am having problems with the "merged cell" part of the formula, I think.

Thanks,
Jim

 

[T. Valko]

If you have 365 columns then you must be using Excel 2007.

Row 2 is the "day of month" number (1,2,3...30,31,1,2,3...etc).

In row 2 enter the actual dates then you can format the cells to display
just the day of the month.

If your days start in cell A2, enter the date 1/1/2009 in A2. Drag across to
cell NA2.

With the range A2:NA2 selected
Right click within the selected range
Format Cells>Custom
Type: d
OK

The cells now display the day number of the months
1,2,3...30,31,1,2,3...etc.

Now, to apply the conditional formatting....
Select the range A3:NA22
Goto Home tab>Styles>Conditional Formatting>Manage rules>New rule>Use a
formula to determine which cells to format
Enter this formula in the box below:
=A$2=TODAY()
Click the Format button
Select the desired style(s)
OK out

 

[BaseballFan]

In row 2 enter the actual dates then you can format the cells to display
just the day of the month.

Simplistically brilliant, thanks. :^)

 

[T. Valko]

You're welcome. Thanks for the feedback!