Simple Conditional Formatting Question

[arlene_fool]

I need a conditional format that will light up a cell if there is supposed to be something there and there isnt.
A & B are start and end dates, C-J are months, i want a formula that will light up the cells under c-j if they are 0 but are between the start and end dates.

I can light them up if they are after the start date, and have a 0 value with this formula "=IF ($J1>0,(I F($J1<Sheet1!A$1,(I F(L1<1,TRUE,FALSE)),FALS E)),FALSE)"

but i need to it to stop after the end date, and i cant figure that out.

please help. thank you

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Rental Start Date	Rental End Date	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug
2	1-May-11	30-Aug-11					$ 500.00	$ 500.00	$ 500.00	$ 250.00
3	1-Mar	11-Sep			$ 500.00	$ 500.00	$ 500.00
Sheet1

Excel 2010

 

[AlphaFrog]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Rental Start Date	Rental End Date	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	1-May-11	30-Aug-11					$500.00	$500.00	$500.00	$250.00
3	1-Mar	11-Sep			$500.00	$500.00	$500.00
Sheet

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2	1. / Formula is =AND(MONTH($A2)<=COLUMN()-2, MONTH($B2)>=COLUMN()-2,LEN(C2)=0)	Abc

 

[arlene_fool]

that doesnt want to work...i think it might be cause of the len at the end. there are actually formulas in each of the blank cell, i didnt think they would make a difference so i didnt post it. sorry, please see below.

Excel Workbook
	J	K	L	M	N	O	P	Q	R	S	T	U
2	Rental Start Date	Rental End Date	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct
3	1-May-11	30-Aug-11		$ -	$ -	$ -	$ 500.00	$ 500.00	$ 500.00	$ 500.00	$ -	$ -
4	1-Mar	11-Sep		$ -	$ -	$ -	$ -	$ 500.00	$ 500.00	$ 500.00	$ 500.00	$ -
Active

Excel 2010

Cell Formulas
Range	Formula
Q3	=IF($J3<40725,(IF($K3>40694,P3,0)),0)
R3	=IF($J3<40756,(IF($K3>40724,Q3,0)),0)
R4	=IF($J4<40756,(IF($K4>40724,Q4,0)),0)
S3	=IF($J3<40787,(IF($K3>40755,R3,0)),0)
S4	=IF($J4<40787,(IF($K4>40755,R4,0)),0)
T3	=IF($J3<40817,(IF($K3>40786,S3,0)),0)
T4	=IF($J4<40817,(IF($K4>40786,S4,0)),0)
U3	=IF($J3<40848,(IF($K3>40816,T3,0)),0)
U4	=IF($J4<40848,(IF($K4>40816,T4,0)),0)
M3	=IF($J3<40603,(IF($K3>40574,L3,0)),0)
M4	=IF($J4<40603,(IF($K4>40574,L4,0)),0)
N3	=IF($J3<40634,(IF($K3>40602,M3,0)),0)
N4	=IF($J4<40634,(IF($K4>40602,M4,0)),0)
O3	=IF($J3<40664,(IF($K3>40633,N3,0)),0)
O4	=IF($J4<40664,(IF($K4>40633,N4,0)),0)
P4	=IF($J4<40695,(IF($K4>40663,O4,0)),0)

 

[AlphaFrog]

You could change the CF formula to this...
Formula is =AND(MONTH($A2)<=COLUMN()-2, MONTH($B2)>=COLUMN()-2,C2=0)

Also, the two in the formula is used to convert the column number to the Month number. In your original example, column C (column 3) is January. So COLUMN()-2 means Column(3)-2 = Month (1) or January.

In your most recent example, you have column L (column 12) as January. So you would need to change the 2s to 11s to make column 12 = Month(1) e.g.
Formula is =AND(MONTH($J2)<=COLUMN()-11, MONTH($K2)>=COLUMN()-11,C2=0)

 

[arlene_fool]

it lights up the whole area now...but i want it to not light up if there is a value there...

Excel Workbook
	J	K	L	M	N	O	P	Q	R	S	T
2	Rental Start Date	Rental End Date	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep
3	1-May-11	30-Aug-11		$ -	$ -	$ -	$ 500.00	$ 500.00	$ 500.00	$ 500.00	$ -
4	1-Mar	11-Sep		$ -	$ -	$ -	$ -	$ 500.00	$ 500.00	$ 500.00	$ 500.00
5				$ -	$ -	$ -	$ -	$ -	$ -	$ -	$ -
Active

Excel 2010

Cell Formulas
Range	Formula
Q3	=IF($J3<40725,(IF($K3>40694,P3,0)),0)
Q5	=IF($J5<40725,(IF($K5>40694,P5,0)),0)
R3	=IF($J3<40756,(IF($K3>40724,Q3,0)),0)
R4	=IF($J4<40756,(IF($K4>40724,Q4,0)),0)
R5	=IF($J5<40756,(IF($K5>40724,Q5,0)),0)
S3	=IF($J3<40787,(IF($K3>40755,R3,0)),0)
S4	=IF($J4<40787,(IF($K4>40755,R4,0)),0)
S5	=IF($J5<40787,(IF($K5>40755,R5,0)),0)
T3	=IF($J3<40817,(IF($K3>40786,S3,0)),0)
T4	=IF($J4<40817,(IF($K4>40786,S4,0)),0)
T5	=IF($J5<40817,(IF($K5>40786,S5,0)),0)
M3	=IF($J3<40603,(IF($K3>40574,L3,0)),0)
M4	=IF($J4<40603,(IF($K4>40574,L4,0)),0)
M5	=IF($J5<40603,(IF($K5>40574,L5,0)),0)
N3	=IF($J3<40634,(IF($K3>40602,M3,0)),0)
N4	=IF($J4<40634,(IF($K4>40602,M4,0)),0)
N5	=IF($J5<40634,(IF($K5>40602,M5,0)),0)
O3	=IF($J3<40664,(IF($K3>40633,N3,0)),0)
O4	=IF($J4<40664,(IF($K4>40633,N4,0)),0)
O5	=IF($J5<40664,(IF($K5>40633,N5,0)),0)
P4	=IF($J4<40695,(IF($K4>40663,O4,0)),0)
P5	=IF($J5<40695,(IF($K5>40663,O5,0)),0)

 

[AlphaFrog]

The CF formula in L3 should be this..
Formula is =AND(MONTH($J3)<=COLUMN()-11, MONTH($K3)>=COLUMN()-11,L3=0)

 

[arlene_fool]

that works...for 2011

unfortunatly that row continues on for 3 years...html maker only allowed me to post 13 columns though...