change a color of a cell based on it's contents

[Wolverine]

Hello,

I read many messages about the conditionnal formatting but only three
levels are not enough.

I explain what I want to do :
I've an excell sheet containing some datas taken from the volume
occupied by a database. During the day, the database is growing up,
and in the morning, a cleaning is performed. We pick up at regular
interval during the day.
I would like to color the cell based on the % it content from a dark
green to a dark red.
Something like that :
1-5 : very dark green
6-10 : dark green
11-15 : grenn
16-20 : light green
21-25 : very light green
26-30 : a little bit yellow
...
...
95-99 : dark red

Thx in advance for your help.
B²

 

[hcj]

Hi B,
I don't know of a way around the 3 criteria limit, but
you might consider another approach to your visual signal
question. If it is possible and practical, you might
insert 20 narrow columns next to the percentage field, one
column for each range. Each column cell could be
conditionally colored if the percent value is greater than
the bottom value for that range (e.g. percent=19%, first
four columns would be colored). This would give you
a "horizontal bar" effect, where the "length" of the bar
is a cue to the percent value. The individual columns
could be colored any way you like, the lower valued ones
being in the green area, the higher ones showing redder
color. Just an idea. Hope it helps a little.
By the way, if someone does come up with a way around
the three criteria limit for a cell, you need to think
about changing the font color as well so it contrasts well
against the chosen color.

Good luck. hcj

 

[Dave Peterson]

How do the values change?

If it's from typing/pasting, you could use the worksheet_change event:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myColor As Long
Dim myRng As Range
Dim myCell As Range

Set myRng = Me.Range("a:a")

If Intersect(Target, myRng) Is Nothing Then Exit Sub

For Each myCell In Intersect(Target, myRng).Cells
With myCell
Select Case .Value
Case Is < 0.05: myColor = 34
Case Is < 0.1: myColor = 33
Case Is < 0.15: myColor = 32
Case Is < 0.2: myColor = 31
Case Else
myColor = xlNone
End Select
.Interior.ColorIndex = myColor
End With
Next myCell

End Sub

Change the range to match what you need and add a few more lines in that "Select
Case" portion and change the colors to what you want.

When I'm working with colors, I'll record a macro and set the color of a few
cells to get that number.

You may want to refer to David McRitchie's notes about colors at:
http://www.mvps.org/dmcritchie/excel/colors.htm

 

[Dave Peterson]

Right click on the worksheet tab that should have this behavior. Select view
code and paste this in.