CODEWITHSUNDEEP
vbaexcel
zabimaru
zabimaru

Reputation: 101

VBA Excel Won't Change Cell to the Right Color

I am new with using VBA macros in excel, and I am trying to change the background color of a cell based on the condition that another cell is not empty. I thought I had it figured out, but I must be doing something wrong, because no matter what color I specify the 'Interior.Color' to change to, it turns the cell an ugly blue color. Here's the code:

Sub Hello()
    MsgBox ("Hello, world!")

    Dim shSource As Worksheet
    Dim shDest As Worksheet

    Set shSource = ThisWorkbook.Sheets("Box Channel Tracking")
    Set shDest = ThisWorkbook.Sheets("Box Channel Schematic")

    If shSource.Range("C176").Value <> "" Then
        shDest.Range("E8").Interior.Color = RGB(255, 255, 255)
        shDest.Range("E8").Interior.Pattern = xlSolid   
    Else
        shDest.Range("E8").Interior.Color = Red
        shDest.Range("E8").Interior.Pattern = xlSolid  
    End If
End Sub

Upvotes: 2

Views: 2784

Answers (2)

datatoo
datatoo

Reputation: 2049

Check that the color palette for excel has not been changed
Depending upon your version you can reset it to a standard palette

in 2003 Tools>Options>Color http://support.microsoft.com/kb/288412 describes how to do this also

Or in a later version you may have a custom theme applied that is causing issues

Upvotes: 0

mechanical_meat
mechanical_meat

Reputation: 169304

You've got to use either the color's index or the appropriate RGB value. Change the first line of the else branch to:

        shDest.Range("E8").Interior.Color = RGB(255, 0, 0)

Full code sample:

Sub Hello() 
    MsgBox ("Hello, world!")

    Dim shSource As Worksheet
    Dim shDest As Worksheet

    Set shSource = ThisWorkbook.Sheets("Box Channel Tracking")
    Set shDest = ThisWorkbook.Sheets("Box Channel Schematic")

    If shSource.Range("C176").Value <> "" Then
        shDest.Range("E8").Interior.Color = RGB(255, 255, 255)
        shDest.Range("E8").Interior.Pattern = xlSolid
    Else
        shDest.Range("E8").Interior.Color = RGB(255, 0, 0) '<-modified
        shDest.Range("E8").Interior.Pattern = xlSolid
    End If
End Sub

Upvotes: 3

Related Questions