Help on shorter way to write IF statement

[SPLUCENA]

Hi All,

I write this code, does the job but seems long. Is there a shorter way to rewrite this code and still do its job?

            If ActiveSheet.Range("B2").Value = "Red" Then
                ActiveSheet.Shapes("shEAC").Fill.ForeColor.RGB = RGB(255, 0, 0)
            Else
            If ActiveSheet.Range("B2").Value = "Green" Then
                ActiveSheet.Shapes("shEAC").Fill.ForeColor.RGB = RGB(0, 255, 0)
            Else
            If ActiveSheet.Range("B2").Value = "Yellow" Then
                ActiveSheet.Shapes("shEAC").Fill.ForeColor.RGB = RGB(255, 255, 0)
            Else
            If ActiveSheet.Range("B2").Value = "Orange" Then
                ActiveSheet.Shapes("shEAC").Fill.ForeColor.RGB = RGB(255, 69, 0)
            End If
            End If
            End If
            End If
           
            If ActiveSheet.Range("B3").Value = "Red" Then
                ActiveSheet.Shapes("Logged").Fill.ForeColor.RGB = RGB(255, 0, 0)
            Else
            If ActiveSheet.Range("B3").Value = "Green" Then
                ActiveSheet.Shapes("Logged").Fill.ForeColor.RGB = RGB(0, 255, 0)
            Else
            If ActiveSheet.Range("B3").Value = "Yellow" Then
                ActiveSheet.Shapes("Logged").Fill.ForeColor.RGB = RGB(255, 255, 0)
            Else
            If ActiveSheet.Range("B3").Value = "Orange" Then
                ActiveSheet.Shapes("Logged").Fill.ForeColor.RGB = RGB(255, 69, 0)
            End If
            End If
            End If
            End If

Thanks for your help.

Rgds,

splucena

 

[Oscar Cronquist]

Select Case ActiveSheet.Range("B2").Value
    Case Is = "Red"
        ActiveSheet.Shapes("shEAC").Fill.ForeColor.RGB = RGB(255, 0, 0)
    Case Is = "Green"
       ActiveSheet.Shapes("shEAC").Fill.ForeColor.RGB = RGB(0, 255, 0)
    Case Is = "Yellow"
       ActiveSheet.Shapes("shEAC").Fill.ForeColor.RGB = RGB(255, 255, 0)
   Case Is = "Orange"
       ActiveSheet.Shapes("shEAC").Fill.ForeColor.RGB = RGB(255, 69, 0)
End Select
Select Case ActiveSheet.Range("B3").Value
    Case Is = "Red"
        ActiveSheet.Shapes("shEAC").Fill.ForeColor.RGB = RGB(255, 0, 0)
    Case Is = "Green"
       ActiveSheet.Shapes("shEAC").Fill.ForeColor.RGB = RGB(0, 255, 0)
    Case Is = "Yellow"
       ActiveSheet.Shapes("shEAC").Fill.ForeColor.RGB = RGB(255, 255, 0)
   Case Is = "Orange"
       ActiveSheet.Shapes("shEAC").Fill.ForeColor.RGB = RGB(255, 69, 0)
End Select

 

[steve the fish]

This is a possible:

Dim arrRng, arrShp, i As Long

arrRng = Range("B2:B3")
arrShp = Array("shEAC", "Logged")

For i = LBound(arrRng, 1) To UBound(arrRng, 1)
    Select Case arrRng(i, 1)
        Case "Red"
            ActiveSheet.Shapes(arrShp(i - 1)).Fill.ForeColor.RGB = RGB(255, 0, 0)
        Case "Green"
            ActiveSheet.Shapes(arrShp(i - 1)).Fill.ForeColor.RGB = RGB(0, 255, 0)
        Case "Yellow"
            ActiveSheet.Shapes(arrShp(i - 1)).Fill.ForeColor.RGB = RGB(255, 255, 0)
        Case "Orange"
            ActiveSheet.Shapes(arrShp(i - 1)).Fill.ForeColor.RGB = RGB(255, 69, 0)
    End Select
Next

 

[jmacleary]

This is a little shorter:

            If ActiveSheet.Range("B2").Value = "Red" Then
                ActiveSheet.Shapes("shEAC").Fill.ForeColor.RGB = RGB(255, 0, 0)
            ElseIf ActiveSheet.Range("B2").Value = "Green" Then
                ActiveSheet.Shapes("shEAC").Fill.ForeColor.RGB = RGB(0, 255, 0)
            ElseIf ActiveSheet.Range("B2").Value = "Yellow" Then
                ActiveSheet.Shapes("shEAC").Fill.ForeColor.RGB = RGB(255, 255, 0)
            ElseIf ActiveSheet.Range("B2").Value = "Orange" Then
                ActiveSheet.Shapes("shEAC").Fill.ForeColor.RGB = RGB(255, 69, 0)
            End If
           
            If ActiveSheet.Range("B3").Value = "Red" Then
                ActiveSheet.Shapes("Logged").Fill.ForeColor.RGB = RGB(255, 0, 0)
            ElseIf ActiveSheet.Range("B3").Value = "Green" Then
                ActiveSheet.Shapes("Logged").Fill.ForeColor.RGB = RGB(0, 255, 0)
            ElseIf ActiveSheet.Range("B3").Value = "Yellow" Then
                ActiveSheet.Shapes("Logged").Fill.ForeColor.RGB = RGB(255, 255, 0)
            ElseIf ActiveSheet.Range("B3").Value = "Orange" Then
                ActiveSheet.Shapes("Logged").Fill.ForeColor.RGB = RGB(255, 69, 0)
            End If