Conditional Formatting Formula

[Dummy Excel]

Hi,
I have a spreadsheet with the date in row 1 across the top and then a list of our cars in column A going down.

The aim is to have our mechanics get some touch time on the cars at least once a week (min once every 7 days)

Is there a conditional formatting formula that will automatically highlight the cell based on the last time it was touched (CP is entered in the cell when it was compeleted) and todays date?

Example of spreadsheet:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		1/04/2013	2/04/2013	3/04/2013	4/04/2013	5/04/2013	6/04/2013	7/04/2013	8/04/2013	9/04/2013	10/04/2013	11/04/2013	12/04/2013	13/04/2013	14/04/2013	15/04/2013
2	Car1	CP
3	Car2		CP
4	Car3			CP
5	Car4				CP
6	Car5					CP
7	Car6						CP
8	Car7							CP
Sheet1

The red highlight is what needs to highlight via the conditional formatting, and obviously amend once a CP is entered in within the next 7 days

 

[Akashwani]

Hi,

Does this work for you?...

Sample data...

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		01 Apr	02 Apr	03 Apr	04 Apr	05 Apr	06 Apr	07 Apr	08 Apr	09 Apr	10 Apr	11 Apr	12 Apr	13 Apr	14 Apr	15 Apr
2	Car1	CP							6	7	8	9	10	11	12	13
3	Car2		CP						5	6	7	8	9	10	11	12
4	Car3			CP					4	5	6	7	8	9	10	11
5	Car4				CP				3	4	5	6	7	8	9	10
6	Car5					CP			2	3	4	5	6	7	8	9
7	Car6						CP		1	2	3	4	5	6	7	8
8	Car7							CP	0	1	2	3	4	5	6	7
9
Sheet7

The formula in I2 is counting the number of blank cells since the last cell with data.
This formula can be used for Conditional Formatting with a slight addition...

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		01 Apr	02 Apr	03 Apr	04 Apr	05 Apr	06 Apr	07 Apr	08 Apr	09 Apr	10 Apr	11 Apr	12 Apr	13 Apr	14 Apr	15 Apr
2	Car1	CP							TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
3	Car2		CP						FALSE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
4	Car3			CP					FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
5	Car4				CP				FALSE	FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE
6	Car5					CP			FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	TRUE	TRUE
7	Car6						CP		FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE	TRUE
8	Car7							CP	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	TRUE
9
Sheet7

The fomula now in I2 can be used to set your CF.

Press alt H L N
Click Use a formula etc
Pasted this formula into the white box...

=COLUMN() - 1 - MATCH(REPT("z",255), OFFSET(I2, 0, 1 - COLUMN(), 1, COLUMN() - 1), 1)>=6

Click Format, select your colour, click OK, OK.

I got the above formula from shg here....

Count Blank Empty Cells

I hope this help.

Good luck.

Ak