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

SteelyDanFan Published at Dev

SteelyDanFan

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

Arthur Samarcos

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

Collected from the Internet

Please contact [email protected] to delete if infringement.