Change Row & Cell Colours Depending Upon Certain Values

[jaredino]

Hi,

I have a spreadsheet with columns A to T in use. There needs to be scope for 100's of rows.

I would like to know how to do the following, please:

If there is a value (a date) in a row in column J, I would like to turn the relevant row green.

I would also like column K to show the number of days that have passed since the date in column J (I have already done this with the following formula =TODAY()-J10). If this number (the number in column K) is more than 5, I want the cell to turn red. Finally, if there is a date in column N, I would like the cell in column K to turn back/stay green.

Is this possible?

Thanks
Jared

[BadlySpelledBuoy]

Potentially easy enough to accomplish with Conditional Formatting.

If you could post a sample workbook with the expected cells manually highlighted to make sure we know exactly what you expect then your solution won't be far behind.

BSB

[jaredino]

Hopefully I've managed to upload the attached example.

Thanks

[CAntosh]

You can do this with two conditional formatting rules. First, select all of your entry rows and enter the following CF formula rule:

=NOT(ISBLANK($J2))

And select a green fill. Then, select all of your rows but only column K and enter the following CF formula rule:

=AND(ISBLANK($N2),$K2>5)

And select a red fill. Try those out and let me know if there are any issues.