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.

 

[Spraggs]

Brilliant, thanks for your help.

 

[Spraggs]

VoG
Now this works a treat is it possible to add something else....
Any cell between C3:BJ37 that is equal to cell H43 to have a diagonal border (e.g. X). I could create a simple macro if need be to change a cell and then return it to original value if this is required to trigger change.

Regards Jase

 

[VoG]

Try

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")
    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
    If c.Value = Range("H43").Value Then
        c.Borders(xlDiagonalDown).Weight = xlThin
        c.Borders(xlDiagonalUp).Weight = xlThin
    End If
Next c
End Sub

 

[Spraggs]

Thanks Peter