macro to color all text red in worksheet if within certain date

[Guest]

I understand that you can use the Conditional formatting tool to change the
font of a cell based on the actual content of the cell, but I'm looking for a
macro to change the font color of the entire sheet if the date of a
particular cell is within a certain time.

For example on cell A1 would = date(2007,9,12). The condition would be if
the date in A1 is greater than 9/12/2007 then the macro would color the
entire worksheet red.

Thanks.

 

[Dave Peterson]

Select all the cells on the worksheet (or just the range you want).

And with A1 the activecell
format|conditional formatting|
Formula is:
=$A$1>DATE(2007,9,12)

The $a$1 tells excel to use A1 as the cell to use for all the cells you've
selected.

And format it the way you want.

 

[Gary Keramidas]

this may do what you want, just put this line in your code:

if range("A1").Value > dateserial(2007,9,12) then cells.Font.Colorindex = 3

 

[Guest]

Great, thank you Dave.

Select all the cells on the worksheet (or just the range you want).

And with A1 the activecell
format|conditional formatting|
Formula is:
=$A$1>DATE(2007,9,12)

The $a$1 tells excel to use A1 as the cell to use for all the cells you've
selected.

And format it the way you want.