vba msgbox cells based on cell colour

Question

I'm trying to create a macro to msgbox all cells with red fill on when opening a spreadsheet, I currently have the below but it's not even displaying a msgbox, any ideas?

Sub test()

Dim ws As Worksheet
Set ws = Sheets("Month End Tasks")
Dim i As Integer
i = 1
Do Until i = 11
    If ws.Range("C" & i).Interior.Color = RGB(255, 0, 0) Then
        MsgBox "C" & i & "  is red!!"
    End If
i = i + 1
Loop

End Sub

Thanks,

Presumably the cells are not red (or at least not 255,0,0)? They aren't coloured by conditional formatting? — SJR, Sep 05 '17 at 13:57
They are conditionally formatted based on the date so anything that is due to be done before today gets coloured red so I want it so when the SS is opened it tells the user all the overdue items as at today, does this make sense? — Brentford123, Sep 05 '17 at 14:15
`Interior.Colour` won't detect CF colours, so you'll probably have to use the condition you used for CF to check. In recent versions of Excel I think you can use the `DisplayFormat` property. — SJR, Sep 05 '17 at 14:19

Vityata · Answer 1 · 2017-09-05T14:30:50.823

Try to see the RGB colors of the C1 to C11, using the following:

Sub WriteCellColor()

    Dim ws      As Worksheet
    Dim i       As Long

    Set ws = ActiveSheet

    i = 1
    Do Until i = 11
        Debug.Print ws.Range("C" & i).Address
        Debug.Print getRGB2(ws.Range("C" & i).Interior.Color)
        i = i + 1
    Loop

End Sub

Public Function getRGB2(l_long) As String

    Dim c   As Long
    Dim R   As Long
    Dim G   As Long
    Dim B   As Long

    c = l_long
    R = c Mod 256
    G = c \ 256 Mod 256
    B = c \ 65536 Mod 256
    getRGB2 = "R=" & R & ", G=" & G & ", B=" & B

End Function

It would print a summary of the RGB colors in the immediate window. Like this:

$C$1
R=255, G=0, B=0
$C$2
R=255, G=255, B=0

An easier method is simpy to select the cell with the color and to press

Format Cells>Patterns>Colors>Custom:

Then the RGB values are visible.

Concerning the conditional formatting, it is probably the best to apply the same logic as in the condition: Excel 2007 conditional formatting - how to get cell color?

vba msgbox cells based on cell colour

1 Answers1