CODEWITHSUNDEEP
vbaexcelcolorscellmsgbox
Brentford123
Brentford123

Reputation: 91

vba msgbox cells based on cell colour

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,

Upvotes: 0

Views: 2523

Answers (1)

Vityata
Vityata

Reputation: 43595

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:

enter image description here

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?

Upvotes: 2

Related Questions