CODEWITHSUNDEEP
excelvba
SteelyDanFan
SteelyDanFan

Reputation: 79

How can I write this ElseIf as a Case statement in Excel-VBA?

Formats each cell in the plotting area according to text in cell, but would like to know how to do the same with a Case statment.

For Each Cell In Range("B11:AB200")
    If Cell.Value = ""
        Cell.Interior.Color = RGB(230, 230, 230) Then
    ElseIf Cell.Value = "tp" Then
        Cell.Interior.Color = RGB(0, 51, 150)
        Cell.Font.Color = RGB(170, 170, 170)
        Cell.Font.FontStyle = "Normal"
    ElseIf Cell.Value = "ot" Then
        Cell.Interior.Color = RGB(200, 0, 0)
        Cell.Font.Color = RGB(170, 170, 170)
        Cell.Font.FontStyle = "Normal"
    ElseIf Cell.Value = "lu" Then
        Cell.Interior.Color = RGB(180, 180, 50)
        Cell.Font.Color = RGB(120, 120, 120)
        Cell.Font.FontStyle = "Normal"
    Else
        Cell.Interior.Color = RGB(255, 255, 0)
        Cell.Font.Color = RGB(120, 120, 120)
        Cell.Font.FontStyle = "Normal"
    End If
Next Cell

I have tried the following but get a 'type mistmacth'

For Each Cell In Range("B11:AB200")
Select Case Range("B11:AB200")
    Case Cell.Value = ""
        Cell.Interior.Color = RGB(230, 230, 230)
    Case Cell.Value = "tp"
        Cell.Interior.Color = RGB(0, 51, 150)
        Cell.Font.Color = RGB(170, 170, 170)
        Cell.Font.FontStyle = "Normal"
    Case Cell.Value = "ot"
        Cell.Interior.Color = RGB(200, 0, 0)
        Cell.Font.Color = RGB(170, 170, 170)
        Cell.Font.FontStyle = "Normal"
    Case Cell.Value = "lu"
        Cell.Interior.Color = RGB(180, 180, 50)
        Cell.Font.Color = RGB(120, 120, 120)
        Cell.Font.FontStyle = "Normal"
    Case Else
        Cell.Interior.Color = RGB(255, 255, 0)
        Cell.Font.Color = RGB(120, 120, 120)
        Cell.Font.FontStyle = "Normal"
End Select
Next Cell

Upvotes: 1

Views: 378

Answers (1)

Arthur Samarcos
Arthur Samarcos

Reputation: 3299

Just a few tweaks.

    For Each Cell In Range("B11:AB200")
Select Case Cell.value
    Case ""
        Cell.Interior.Color = RGB(230, 230, 230)
    Case "tp"
        Cell.Interior.Color = RGB(0, 51, 150)
        Cell.Font.Color = RGB(170, 170, 170)
        Cell.Font.FontStyle = "Normal"
    Case "ot"
        Cell.Interior.Color = RGB(200, 0, 0)
        Cell.Font.Color = RGB(170, 170, 170)
        Cell.Font.FontStyle = "Normal"
    Case "lu"
        Cell.Interior.Color = RGB(180, 180, 50)
        Cell.Font.Color = RGB(120, 120, 120)
        Cell.Font.FontStyle = "Normal"
    Case Else
        Cell.Interior.Color = RGB(255, 255, 0)
        Cell.Font.Color = RGB(120, 120, 120)
        Cell.Font.FontStyle = "Normal"
End Select
Next Cell

Upvotes: 3

Related Questions