CODEWITHSUNDEEP
excelvba
SQLUser
SQLUser

Reputation: 123

VBA - If cell value ... then multiple THEN statements

Objective is to color cell & change font & italicize font of cell in column F based on value in column A. I have the following code, but when testing this, I keep getting a black cell in F...Strange because if I only have one then statement, it seems to work fine.

Sub test()
    Dim lrow As Long
    lrow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For i = 2 To lrow
        If Cells(i, "A").Value = "TEST" Then
            Cells(i, "F").Interior.Color = RGB(221, 235, 247) And Cells(i, "F").Font.Italic = True And Cells(i, "F").Font.Color = RBG(128, 128, 128)
        End If
    Next i
End Sub

Upvotes: 0

Views: 793

Answers (1)

John Coleman
John Coleman

Reputation: 52008

AND is a logical connective which combines truth values, not something which sequences statements. If you want two or more statements to be executed in the if block -- just put them one after the other without trying to put an AND in between. In other words, use

If Cells(i, "A").Value = "TEST" Then
        Cells(i, "F").Interior.Color = RGB(221, 235, 247)
        Cells(i, "F").Font.Italic = True
        Cells(i, "F").Font.Color = RGB(128, 128, 128)
End If

The code that you had also contained a typo (RBG instead of RGB) that would prevent it from running at all. Using Option Explicit would catch such errors in the compile stage. Fixing that typo, I still found it surprising that your code ran at all. What is happening is an artifact of how VBA uses the same symbol , =, for both assignment and equality testing. This causes VBA to parse

Cells(i, "F").Interior.Color = RGB(221, 235, 247) And Cells(i, "F").Font.Italic = True And Cells(i, "F").Font.Color = RGB(128, 128, 128)

as

Cells(i, "F").Interior.Color = (RGB(221, 235, 247) And (Cells(i, "F").Font.Italic = True) And (Cells(i, "F").Font.Color = RGB(128, 128, 128)))

Since RGB(221, 235, 247) evaluates to 16247773, VBA evaluates your statement as

Cells(i, "F").Interior.Color = (16247773 And False And False)

Which is the same as

Cells(i, "F").Interior.Color = 0

It is this that sets the color to black.

Also -- why not just use conditional formatting for this rather than VBA?

Upvotes: 1

Related Questions