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!

 

[VoG]

See if you can adapt this http://www.vbaexpress.com/kb/getarticle.php?kb_id=90

 

[jberg123]

Actually, can I modify this so that it colours THIS cells according to the integer value in the cell immediately to the right?


Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer



If Not Intersect(Target, Range("A1:A10")) is Nothing Then

Select Case Target

Case 1 To 5

icolor = 6

Case 6 To 10

icolor = 12

Case 11 To 15

icolor = 7

Case 16 To 20

icolor = 53

Case 21 To 25

icolor = 15

Case 26 To 30

icolor = 42

Case Else

'Whatever

End Select



Target.Interior.ColorIndex = icolor

End If

 

[VoG]

Try

Select Case Target.Offset(, 1)

 

[jberg123]

Try

Select Case Target.Offset(, 1)

Did the trick!!! Thanks!

Question now though.

This macro doesn't happen "continously all the time". I have to select cell, F2, ENTER, for colour to change... Can I change this somehow?

Thanks.



Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer



If Not Intersect(Target, Range("G16:G40")) Is Nothing Then

Select Case Target.Offset(, 1)

Case 1

icolor = 4

Case 2

icolor = 35

Case 3

icolor = 44

Case 4

icolor = 6

Case 5

icolor = 2

Case Else

'Whatever

End Select



Target.Interior.ColorIndex = icolor

End If

End Sub

 

[VoG]

That should work automatically if you manually change values. Does that range contain formulas?

 

[jberg123]

That should work automatically if you manually change values. Does that range contain formulas?

Yes it does, cell referenced to another workbook.

 

[VoG]

Try replacing that with

Private Sub Worksheet_Calculate()
Dim icolor As Integer, c As Range
For Each c In Range("G16:G40")
    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

 

[jberg123]

Try replacing that with

Private Sub Worksheet_Calculate()
Dim icolor As Integer, c As Range
For Each c In Range("G16:G40")
    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

Brilliant! Many thanks!

One last question, I want to do this for G16:G40, H16:H40 and I16:I40 at the same time. How do I do this?

 

[VoG]

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