Use Case statement to change colour of date

[Dennistoun Dark]

I am looking to use a Case statement to change the font colour of a date: today's date, a date in the future and a date past.

I have tried the following using Date Today and Now but get an error each time.

'Colour cells based upon value (column J)
Select Case Cell.Offset(0, -2).Value
Case Today
Cell.Offset(0, -2).Interior.FontIndex = 2
Case Is < Today
Cell.Offset(0, -2).Interior.FontIndex = 1
Case Is > Today
Cell.Offset(0, -2).Interior.FontIndex = 4
If Cell.Value = "C" Then Cell.Offset(0, -2).Font.ColorIndex = 1
End Select

The general format of Case is fine because I am using it to change actions in a number of column and rows bed upon txt strings. The column offset from row L is correct as well because I can use Case statements on text strings to change it.

Any help would be appreciated.

Cheers

Frank

 

[Yongle]

TODAY is an EXCEL function

In VBA, the equivalent is DATE

and
cell.Offset(0, -2).Font.ColorIndex = 2

 

[Dennistoun Dark]

I have tried:

'Colour cells based upon value (column J)
Select Case Cell.Offset(0, -2).Value
Case Today
Cell.Offset(0, -2).Interior.ColorIndex = 43
Case Is < Today
Cell.Offset(0, -2).Interior.FontIndex = 1
Case Is > Today
Cell.Offset(0, -2).Interior.FontIndex = 4
If Cell.Value = "C" Then Cell.Offset(0, -2).Font.ColorIndex = 2
End Select

I still get an error

 

[Yongle]

Not sure if your IF statement is in the correct place
- only applies if Case Is > Date

'Colour cells based upon value (column J)

Select Case cell.Offset(0, -2).Value
    Case Date
        cell.Offset(0, -2).[COLOR=#ff0000]Font.ColorIndex [/COLOR]= 2
    Case Is < Date
        cell.Offset(0, -2).Font.ColorIndex = 1
    Case Is > Date
        cell.Offset(0, -2).Font.ColorIndex = 4
        If cell.Value = "C" Then cell.Offset(0, -2).Font.ColorIndex = 1
End Select

When posting code please click on # icon above post window and ...
[ CODE] paste your code between the tags [ /CODE]

 

[dmt32]

Hi,
try this update to your code

'Colour cells based upon value (column J)
    With cell.Offset(0, -2)
        If UCase(cell.Value) = "C" Then
            .Font.ColorIndex = 1
        ElseIf IsDate(.Value) Then
            Select Case .Value
            Case Date
                .Font.ColorIndex = 2
            Case Is < Date
                .Font.ColorIndex = 1
            Case Is > Date
                .Font.ColorIndex = 4
            End Select
        End If
    End With

It is assumed that your Range Object variable Cell has been correctly intialized.

Dave

 

[Dennistoun Dark]

Thanks Dave; now sorted!