Define color of row checking two dates within same cell and text in other cell

**amragl** · 09-26-2011, 06:59 AM

I have the following problem.....

Please Login or Register  to view this content.

In Column2 each cell have two dates with times(when should start and when should finish), and in column4 I have the status of the task.

The conditions to set the color of the row is based on Column2 and Column4. The color code would simulate a traffic light Green for OK, Orange for Aware, and Red for Bad.

If first date of the cell in Column2 is greater than current then color row is Orange.

If first date of the cell in Column2 is greater than current and Column4 is "Started" then color row is green.

If second date of the cell in Column2 is greater than current and text in Column4 is not "Complete" then color row is Red. If text is "Complete" then color row Green.

One more thing to consider is that the second date is not a date, but text like TBC and other things.....

I know if it was one date time on the cell I could use conditional formatting, because there are two dates on the same cell, I think I need the help of a excel macro expert.

Thanks in advance

**quekbc** · 09-26-2011, 07:27 AM

What I would recommend, since you're using Excel 03, is to have a cell for each of those 2 rows, that calculates and gives a value, say 1, 2 or 3 for each of the condition you mentioned above. Then use conditional formatting to format it red orange green.

**amragl** · 09-26-2011, 07:33 AM

Thanks quekbc,

That would make everything a lot easier, but unfortunately I can't change the format.

I have to find the solution for this format.

Thanks for the advise.