Define color of row checking two dates within same cell and text in other cell
I have the following problem.....
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.---------------------------------------------------------- Column1 | Column2 | Column3 | Column4 | Column5 ---------------------------------------------------------- Bla bla | 26/09/2011 10:00 | blabla | Complete| blabla | | | | bla bla | 26/09/2001 11:00 | blabla | | ---------------------------------------------------------- Bla bla | 26/09/2011 11:00 | blabla | | blabla | | | | bla bla | 26/09/2001 11:30 | blabla | | ---------------------------------------------------------- Bla bla | 26/09/2011 12:00 | blabla | Started | blabla | | | | bla bla | 26/09/2001 13:00 | blabla | | ---------------------------------------------------------- Bla bla | 26/09/2011 22:00 | blabla | | blabla | | | | bla bla | 26/09/2001 23:00 | blabla | | ----------------------------------------------------------
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
Last edited by amragl; 09-26-2011 at 07:35 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.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks