Conditional formatting - Dates < or = or > TODAY(), Icon Set (red, Yellow,Green Light)

[sdcc633]

For some reason 2007 Excel is not doing this for me. So, can you help?

I have the following table...Reference date = TODAY()
1st Col. 2nd Col. 3rd Col
[TABLE="class: grid, width: 267"]
<tbody>[TR]
[TD]10/10/13[/TD]
[TD]10/23/13[/TD]
[TD]10/28/13[/TD]
[/TR]
[TR]
[TD]11/6/13[/TD]
[TD]11/8/13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/22/13[/TD]
[TD]11/11/13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/22/13[/TD]
[TD]11/5/13[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I want to compare the 2nd column date with the reference (will change day by day) date and based on 2 things I want excel to do the following:
1. If all 3 columns contain a date, then nothing is put before the date in the second column. Conditional formatting stops
2. No date in the 3rd column, If the date in the 2nd column is > the reference then a red light shows up before the date in the column.
3. No date in the 3rd column, If the date in the 2nd column is = the reference then a yellow light shows up before the date in the column.
4. No date in the 3rd column, If the date in the 2nd column is < the reference then a green light shows up before the date in the column.

I have tried numerous ways but either nothing is happening or screwy results come up.

Suggestions? Thanks!

 

[mumps]

If you're willing to try a macro, this should do what you want.

Sub Test()
    Application.ScreenUpdating = False
    Dim bottomB As Integer
    bottomB = Range("B" & Rows.Count).End(xlUp).Row
    Dim rng As Range
    For Each rng In Range("B2:B" & bottomB)
        If rng.Offset(0, 1) = "" Then
            If rng > Date Then
                rng.Interior.ColorIndex = 3
            ElseIf rng = Date Then
                rng.Interior.ColorIndex = 6
            ElseIf rng < Date Then
                rng.Interior.ColorIndex = 4
            End If
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[sdcc633]

If you're willing to try a macro, this should do what you want.

Sub Test()
    Application.ScreenUpdating = False
    Dim bottomB As Integer
    bottomB = Range("B" & Rows.Count).End(xlUp).Row
    Dim rng As Range
    For Each rng In Range("B2:B" & bottomB)
        If rng.Offset(0, 1) = "" Then
            If rng > Date Then
                rng.Interior.ColorIndex = 3
            ElseIf rng = Date Then
                rng.Interior.ColorIndex = 6
            ElseIf rng < Date Then
                rng.Interior.ColorIndex = 4
            End If
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

==============================

mumps,

Thanks! Never did a MACRO but it is always a good time to learn.

 

[mumps]

Terrific! Did it work for you?

 

[sdcc633]

Didn't try yet but will...yikes!