Define color of row checking two dates within same cell and text in other cell. Excel macros?

0
votes

I have the following problem.....

----------------------------------------------------------
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  |         |        
----------------------------------------------------------

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.

2
excelvbadate

2 Answers

1
votes

You can use conditionitional formatting:

Eg for cell B2, use these formula 

=DATEVALUE(LEFT(B2,FIND(CHAR(10),B2)-1))>NOW()

for first date in cell greater than current

=DATEVALUE(MID(B9,FIND(CHAR(10),B9)+1,99))>NOW()

for second date in cell greater than current

The rest (layering the conditions, factoring in the status) should be straight forward

0
votes

Ok I got sort of confused reading your request but basically you want to conditionally format the cell if the time/date is different from first to second.

What you need is something like the following

With Worksheets("Sheet1")
If .Range("B2") > Now And .Range("D2") <> "Started" Then
   .Range("B2").Interior.Color = vbOrange
Else
   .Range("B2").Interior.Color = vbGreen
End If
End with

Now if you're not comfortable or used to VBA this is a little more complex. But basically you can change the range and sheet names to do whatever you need it to. The "Interior.Color" function will work with vbGreen,vbRed,vbOrange and several more. More than enough to suit your needs.

Hopefully that can get you started. Let me know if you need more help.