Conditional formatting of dates prior to selected date

[Cflaursen]

Hi all

I hope someone can help me with a question concerning conditional formatting.

1. Row 2 Column j-p contains dates (in date format)
2. A2 contains a date

3. Now I want to dimm/light gray all columns (j-P) that has a date prior to the one in A2. (I have an area name for the area including row 2 that is to be dimmed - Name= "Forecast")

Can anyone help me on this one (not selecting light gray)?

Regards CFL

 

[Gerald Higgins]

In 2003....

Select J2:P2
Format
Conditional Formating
Cell Value Is
< $A$2
choose a format, and OK out.

 

[Cflaursen]

Hi Gerald Higgins

Thanks for the reply.

Maybe my description wasn't good enough so I'll elaborate somewhat.

Based on row 2 Column j-p that contains months and I want to conditional format columns (or part of columns depending of the number of rows) for months prior to a month defined in A2 (containing a month).

E.g. gray cells J2-J30 (if thats my number of rows) and J2 is a month prior to the month defined in A2.


I think i minght need some kind of HLookup formula maybe???

Regards CFL

 

[Gerald Higgins]

If ONLY column J contains dates, but you want to apply the formating to columns J:P, depending on the date in col J on each row, use the FORMULA IS option, instead of CELL VALUE IS, and then

=$J2< $A$2
[code]

 

[asking]

Hi Cflaursen,
I think what you want is only possible if you do each column J to P separately ~

Select all Column J conditional format
=$J2< $A$2
Select all Column K conditional format
=$K2< $A$2
Select all Column L conditional format
=$L2< $A$2
Continue rest of columns in like manner

Cheers

 

[Cflaursen]

Hi all

Thanks for the advise.

However in reality i have a large number of columns to format, hence the proposed pr. column approach would be a time consuming task.

Therefor I was looking for a way to format all relevant columns (containing multiple rows) based on just one formula.


Regards CFL

 

[Gerald Higgins]

The solution I made in post #4 can be applied to many columns at the same time.

 

[Cflaursen]

Thank you Higgins. With a Small modification of your formula I got it working