Conditional Formatting - More Than 3 Formats

Make yellow the default background colour, then use conditional formatting for the other 3 colours.
If you need more than 4 colours, you're in VBA territory. Post back if you need / want some code.

Denis

That won't really work. Guess I really need 5 criteria as anything less than or equal to 799.99 should have no colour.

Ok. I've found some code that I adapted and it works to a degree. But it won't change when the values I'm looking at change. The range which I'm doing the conditional formatting on doesn't contain the actual figures, it contains formulae that look at another sheet. I need the formatting to change when the amounts are changed.

The code I'm using is:

Private Sub Worksheet_Change(ByVal Target As Range)
' Multiple Conditional Format
Dim rng As Range
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
Set rng = Range("E10:E208")
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
If Target.Value <= 799.99 Then _
Target.Interior.ColorIndex = 0
If Target.Value > 1499.99 Then Target.Interior.ColorIndex = 20 ' Platinum
If Target.Value > 900.99 And Target.Value <= 1499.99 Then Target.Interior.ColorIndex = 44 ' Gold
If Target.Value > 850.99 And Target.Value <= 900.99 Then Target.Interior.ColorIndex = 15 ' Silver
If Target.Value > 799.99 And Target.Value <= 850.99 Then Target.Interior.ColorIndex = 46 ' Bronze

End Sub

More than 3 conditional formats

Hello:

If you can get by with font changes you can use this trick to get your 5 conditions

Select your range and choose format cells. Select the custom option. In the type field enter

[Red][>1499.99]General;[Green][>900.99]General;General

Next, with your range still selected, choose format conditional formatting.

Condition 1: Cell value <799.99 - No Formatting

Condition 2: Cell value <850.99 - Format Blue

Condition 3: Cell value <900.99 - Format Yellow

This should work

BTW:The yellow font does not show up well on white. You may wish to change it to another color or shade your backgroud. Also, you will need to decide what color you want when a cell value equals one of your target numbers and adjust the values (i.e. Condition 1 might be <=799.99

HTH

lenze

I like lenze's idea -- very elegant. If you decide to keep going the code route, try this option: The break values are in J2:J5, sorted descending. They could just as easily be links to another sheet.
BTW, I used Select Case because it's easier to maintain if you add or remove conditions.
You'll also need this code in a standard module to set the starting formats:
Denis

Is there a VBA solution to add conditional custom number format to solve this requirement:

Cell U3 contains a MIN limit value (for example 75)
Cell U4 contains a MAX1 limit value (160)
Cell U5 contains a MAX2 limit value (166)

The body of spreadsheet contains pairs of number data to be plotted.
I would like to add custom format with symbols in front of the data based upon relation to limit values.

Using one data pair B12 and B13:

Format for cell B12:
If B12<=$U$3, use format "<"#
If B12>=$U$4
and B13>=$U$5, use ">"#,
else use "~"#
else use default number format: fixed, 0 decimal places (this is if $U$3<B12<$U$4)

For B13:
If B13<=$U$3, use format "<"#
If B13>=$U$4, use ">"#
else default format number, fixed, 0 decimal places (this is if $U$3<B13<$U$4)

One more option:
In some cases I will need to conditionally format the cells with the symbol "<" and the actual cell value of B12, as noted above. So if B12 is 70, it would show <70.
In other cases I will need to conditionally format the cell with the symbol and the limit value. So if B12 is 70, it would show <75.

BTW Barry, it worked great! Thanks!

I like lenze's idea -- very elegant. If you decide to keep going the code route, try this option:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim iColor As Integer
' Multiple Conditional Format
Dim rng As Range
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
Set rng = Range("E10:E208")
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
Select Case Target.Value
    Case Is > Range("J2").Value
        iColor = 20
    Case Is > Range("J3").Value
        iColor = 44
    Case Is > Range("J4").Value
        iColor = 15
    Case Is > Range("J5").Value
        iColor = 46
    Case Else
        iColor = 0
End Select
    Target.Interior.ColorIndex = iColor
End Sub

The break values are in J2:J5, sorted descending. They could just as easily be links to another sheet.
BTW, I used Select Case because it's easier to maintain if you add or remove conditions.
You'll also need this code in a standard module to set the starting formats:

Code:

Sub SetFormats()
    Dim iColor As Integer
    Dim c as Range

For Each c in Range("E10:E208")
Select Case c.Value
    Case Is > Range("J2").Value
        iColor = 20
    Case Is > Range("J3").Value
        iColor = 44
    Case Is > Range("J4").Value
        iColor = 15
    Case Is > Range("J5").Value
        iColor = 46
    Case Else
        iColor = 0
End Select
    c.Interior.ColorIndex = iColor
Next c
End Sub

Denis

Click to expand...

Hi SidneyGeek..

How would I alter the code above if I want to change the Font color of a cell if it had 'Y' or 'N' in it?

Declan

Nothing to add except - Great idea Lenze!