Conditional Formatting Macro (5 conditions)

[jberg123]

Got a range G16:G40 with numbers (cell referenced).

Got three numbers in cells X, Y, Z.

I want cells i the range to be Green if cell value > X, Light Green if between Y and X, Orange if between Z and Y, Red if less than Z, and no format at all if cell is empty.

I can't use excel's standard conditional formatting since it can only handle 3 conditinos (or 4, if you will). I have 5.

Anyone have a good macro for this? Would be most helpful and appreciated.

Thanks!

 

[jberg123]

Try

Private Sub Worksheet_Calculate()
Dim icolor As Integer, c As Range
For Each c In Range("G16:I40")
    Select Case c.Offset(, 1)
        Case 1: icolor = 4
        Case 2: icolor = 35
        Case 3: icolor = 44
        Case 4: icolor = 6
        Case 5: icolor = 2
    End Select
    c.Interior.ColorIndex = icolor
Next c
End Sub

Problem is between the column ranges we have the numbers deciding the colours (offset 1)

 

[VoG]

I'm not sure that I follow. Can you explain exactly what should happen.

 

[jberg123]

I'm not sure that I follow. Can you explain exactly what should happen.

GHIJKL

Sorry, was being stupid in an earlier post.

I meant to say that I need to apply the formatting code to column G, I, K
(not adjacent)

Column G's formatting is decided by integer values in column H, etc

 

[VoG]

Try

Private Sub Worksheet_Calculate()
Dim icolor As Integer, c As Range
For Each c In Range("G16:G40,I16:I40,K16:K40")
    Select Case c.Offset(, 1)
        Case 1: icolor = 4
        Case 2: icolor = 35
        Case 3: icolor = 44
        Case 4: icolor = 6
        Case 5: icolor = 2
    End Select
    c.Interior.ColorIndex = icolor
Next c
End Sub