Conditional Format based on dates
 

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

Conditional Format based on dates
 

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 CellsCustom
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 tabStylesConditional FormattingManage rulesNew ruleUse 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

--
Biff
Microsoft Excel MVP


"BaseballFan" wrote in message
...
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

Conditional Format based on dates
 

"T. Valko" wrote:
In row 2 enter the actual dates then you can format the cells to display
just the day of the month.

Simplistically brilliant, thanks. :^)

Conditional Format based on dates
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"BaseballFan" wrote in message
...
"T. Valko" wrote:
In row 2 enter the actual dates then you can format the cells to display
just the day of the month.

Simplistically brilliant, thanks. :^)