Using different colors depending on cell value

  • Thread starter Thread starter Gerd
  • Start date Start date
G

Gerd

I am working on a price list and would like to "highlight" the various
prices with different colors.
If a cell has an amount from $0-10 color is black, $10-19.99 green, $
20-29.99 blue, $30+ red.
Up to how many levels could I have (as I would want more then the ones
specified above)? I seem to remember that 7 levels is the maximum (which
would probably work for me).
Thanks in advance.
Gerd
 
Hi Gerd

you can use conditional formating. Though this supports only 3
conditions (+ the default condition). See
http://www.contextures.com/xlCondFormat01.html
http://www.cpearson.com/excel/cformatting.htm
for more details.

If you need more - see below:
------
If you only want to apply different FONT colors based on NUMBERS, you
can define up to 6 different styles. See:
http://www.mcgimpsey.com/excel/conditional6.html
for instructions how to do it

For everything else you'll need VBA code (e.g. process the
worksheet_change event and apply your format based on the cell values).
The following will color the entry in cell A1:A100 based on its value:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
Select Case .Value
Case "Red": .Interior.ColorIndex = 3
Case "Blue": .Interior.ColorIndex = 10
'etc.
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub
 
Hi Frank,
thanks for your fast reply. However, the formatting does not seem to work as
in the example. Basically I just copied the example from the web-page and
pasted it into Format/Cell/Number/Custom. I assume instead of the word
General I should use another format for Numbers with 2 decimals.

When I enter any values the color does not change at all. I assume it has
something to do with that the cell contents is asumed to be text (as the
numbers are left justified). However, changing the text to a number changes
the color to black.

What do I do wrong?
 
Back
Top