Vba for 5 cond format in a range

[Spraggs]

Hello All,
I'm after some Vba because I require five conditional formats and I'm working with excel 2003.

My range is C3:BJ37 and cell H43 (Sheet1)

Conditions..
1. <=420 Colour Red
2. <=840 Colour Orange
3. <=1260 Colour Yellow
4. <=1680 Colour Light Green
5. <=21000 Colour Bright Green

(Colours not too important)

Is this possible.

Any help is appreciated.

 

[VoG]

Hi. Try this

Sub Colours()
Dim c As Range
For Each c In Range("C3:BJ37,H43")
    Select Case c.Value
        Case Is <= 420: c.Interior.ColorIndex = 3
        Case Is <= 840: c.Interior.ColorIndex = 44
        Case Is <= 1260: c.Interior.ColorIndex = 6
        Case Is <= 1680: c.Interior.ColorIndex = 43
        Case Is <= 21000: c.Interior.ColorIndex = 4
    End Select
Next c
End Sub

 

[Spraggs]

Thank You.
Is this entered by Alt + F11 ? If so do I have to run a macro after any changes.

 

[VoG]

If you want it to run automatically, do the cells contain formulas or do you change them manually?

 

[Spraggs]

Peter,
The only cell that changes is H43 and this is automatically (by formula) the rest are fixed. Could I have it where the text in cells C3:BJ37 are the same colour as fill (hence text not visible). Thanks for your time.

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Calculate()
With Range("H43")
    Select Case .Value
        Case Is <= 420: .Interior.ColorIndex = 3
        Case Is <= 840: .Interior.ColorIndex = 44
        Case Is <= 1260: .Interior.ColorIndex = 6
        Case Is <= 1680: .Interior.ColorIndex = 43
        Case Is <= 21000: .Interior.ColorIndex = 4
    End Select
End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
For Each c In Range("C3:BJ37,H43")
    Select Case c.Value
        Case Is <= 420: c.Interior.ColorIndex = 3: c.Font.ColorIndex = 3
        Case Is <= 840: c.Interior.ColorIndex = 44: c.Font.ColorIndex = 44
        Case Is <= 1260: c.Interior.ColorIndex = 6: c.Font.ColorIndex = 6
        Case Is <= 1680: c.Interior.ColorIndex = 43: c.Font.ColorIndex = 43
        Case Is <= 21000: c.Interior.ColorIndex = 4: c.Font.ColorIndex = 4
    End Select
Next c
End Sub

 

[Spraggs]

Tried that Peter but got no colours or changes at all?

 

[VoG]

It works for me. Perhaps events are disabled. In the code window press CTRL + G then in the Immediate Window type

Application.EnableEvents=True

and press Enter.

The code will only run when the sheet calculates or you change a value.

 

[Spraggs]

Got this working fine Peter.

What happens if I need to change these values?
Tried changing and saving but colours didn't change.

Thanks Jase

 

[VoG]

If you mean that you've changed the values in the code, you need to change the value in any cell to trigger the code run.