I want to make a cell change colour based on a number of different criteria:

Cell A1 Has an incident date

B1 Displays the date 14 days after the incident date.

Cell C1 will have a car make in, Cell D1 has an examined yes or no option. C1 and D1 will always be completed.

However there may be a car in Cell E1, G1, I1, with examined options in cells F1, H1, J1.

I would like cell B1 to be formatted in the following ways:

Less than 2 days from todays date until due date - red fill

3 to 7 days from todays date until due date - orange fill

8 to 14 days from todays date until due date - green fill

But not if D1 is YES (blank or No then must change), and there is nothing entered in E1, G1 I1.

If E1, G1 or I1 have a car entered then the corresponding examined box must be in the same state as described for D1.

Is this possible? if so how please?

I know its a little complicated!

Still working on this problem though.

If you have 50 different shaded of gray, all with different values, you could perform this. Just start from the least significant, in this case .00-.99, then 1 to 1.99, then 2 though 2.99. Sometimes significance isnt' really a numeric value, but a number of different cases, so for this we would look to such formulas as:

=if(and(a1=yes,b1=yes)) = GREEN CELL (as far as we are concerned, every cell should be a GREEN CELL)

Then our next formula checks to disqualify the green cells, as in:

=if(and(a1=yes,b1=no)) = RED CELL

Then we would check for the next qualifying (in this case, disqualifying), but this time we will turn it yello, because it can still be green, and it it isn't RED. A YELLOW CELL maybe found with:

=if(and(a1=no,b1=yes)) = YELLOW CELL

Is this where you are having problems? Let us know so you can expand on the scope of the project.

what you have written makes logical sense and taking this I am going to try and assemble it all into a coherent (and working) set of conditional formatting.

I have a week away from it all, so will try on my return. Thanks again,

Have fun!

Thanks again