# Conditional Formatting Formula

#### Dummy Excel

##### Well-known Member
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?

Excel 2007
ABCDEFGHIJKLMNOP
11/04/20132/04/20133/04/20134/04/20135/04/20136/04/20137/04/20138/04/20139/04/201310/04/201311/04/201312/04/201313/04/201314/04/201315/04/2013
2Car1CP
3Car2CP
4Car3CP
5Car4CP
6Car5CP
7Car6CP
8Car7CP
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

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Hi,

Does this work for you?...

Sample data...

Excel Workbook
ABCDEFGHIJKLMNOPQ
101 Apr02 Apr03 Apr04 Apr05 Apr06 Apr07 Apr08 Apr09 Apr10 Apr11 Apr12 Apr13 Apr14 Apr15 Apr
2Car1CP678910111213
3Car2CP56789101112
4Car3CP4567891011
5Car4CP345678910
6Car5CP23456789
7Car6CP12345678
8Car7CP01234567
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
ABCDEFGHIJKLMNOPQ
101 Apr02 Apr03 Apr04 Apr05 Apr06 Apr07 Apr08 Apr09 Apr10 Apr11 Apr12 Apr13 Apr14 Apr15 Apr
2Car1CPTRUETRUETRUETRUETRUETRUETRUETRUE
3Car2CPFALSETRUETRUETRUETRUETRUETRUETRUE
4Car3CPFALSEFALSETRUETRUETRUETRUETRUETRUE
5Car4CPFALSEFALSEFALSETRUETRUETRUETRUETRUE
6Car5CPFALSEFALSEFALSEFALSETRUETRUETRUETRUE
7Car6CPFALSEFALSEFALSEFALSEFALSETRUETRUETRUE
8Car7CPFALSEFALSEFALSEFALSEFALSEFALSETRUETRUE
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

Replies
6
Views
294
Replies
3
Views
1K
Replies
65
Views
3K
Replies
20
Views
2K
Replies
5
Views
602

1,203,198
Messages
6,054,068
Members
444,700
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back