Conditional formatting of dates prior to selected date

Cflaursen

New Member
Joined
Apr 6, 2011
Messages
17
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
 
Last edited:

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
In 2003....

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

Cflaursen

New Member
Joined
Apr 6, 2011
Messages
17
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

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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
Code:
=$J2< $A$2
[code]
 

asking

Board Regular
Joined
Sep 5, 2007
Messages
226

ADVERTISEMENT

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

New Member
Joined
Apr 6, 2011
Messages
17
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

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
The solution I made in post #4 can be applied to many columns at the same time.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,818
Messages
5,525,077
Members
409,618
Latest member
gkllc

This Week's Hot Topics

Top