Vba code to color format cells depending on 3 outcomes

[Alpacino]

Hi wondering what would the code be color cells in if
Values is 0 to 40 cell color red
41 to 60 yellow
61 to 100 green

I have used if then but it only uses the first 2 ifs.
Anyone help please.
Thanks
Alan

 

[JackDanIce]

Think you need to use a SELECT CASE statement

Have a read of:
http://www.ozgrid.com/VBA/select-case.htm

 

[klarowe]

Not sure if you want this to automatically run on a sheet selection change (clicking on a cell) or if you want to run it manually, but here is the basic code. Change the cell as needed.

    If Range("A18") <> "" Then
        Select Case Range("A18")
            Case 0 To 40
                Range("A18").Interior.ColorIndex = 3
                Range("A18").Interior.Pattern = xlSolid
            Case 41 To 60
                Range("A18").Interior.ColorIndex = 6
                Range("A18").Interior.Pattern = xlSolid
            Case 61 To 100
                Range("A18").Interior.ColorIndex = 4
                Range("A18").Interior.Pattern = xlSolid
            Case Else
                MsgBox "The number entered is not valid.", vbOKOnly, "Invalid Entry"
        End Select
    Else: End If

 

[Alpacino]

I like to use it as a macro to Call
Also like it to do same thing from B5:B34