conditional cell coloring

[phrodude]

Hi,

I am creating a large matrix analysis of for my company. I need the
value of each cell (0 to 1.0) in my matrix to be displayed in different
colors depending on what value band it falls within. I need at least 6
color bands (e.g values less than 0.1 to be blue, values between 0.1
and 0.2 to be green, and so on.).

To clarify I need the CELL BACKGROUND color to be displayed and not the
text displayed according to what color band the value of the cell is.

I know that I can do 3 background colors (color bands) with
"conditional formating" but this is just not enough. I can also change
the text colors with an "IF" function or "LOOKUP" and get the text to
ba changed with all the colors I need.

Does anyone know how to do this?

or does anyone know how to specify the background color of a cell in a
"funtion" [e.g."(green)" fo text color.] as I can then just drop these
into my formulae.

Thanks

A quick response would be great.


Gav

 

[Bob Phillips]

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[phrodude]

Hi Bob,

Thanks this looks great it's just that I don''t know that much abou
coding really. I have put this code into corret worksheet code module
but not sure where to go from there. Could you please give me a brei
walk through or guide with what to de next please.

Thanks in advance,


Gavi

 

[Bob Phillips]

Hi Gavin,

What it does is to check whether any cell on the target sheet is changed,
and if it is within the range H1:H10, which is defined as a constant at the
start so that you can change it, it then checks what value was entered. If
it is a 1, it changes the cell colour to red, if a 2 to yellow, etc.

Play with it and enter some values in those cells to see the effect, and
then just change the range and values and colours to suit.

BTW, here is a full colour list

Public Enum xlColorIndex
xlCIBlack = 1
xlCIWhite = 2
xlCIRed = 3
xlCIBrightGreen = 4
xlCIBlue = 5
xlCIYellow = 6
xlCIPink = 7
xlCITurquoise = 8
xlCIDarkRed = 9
xlCIGreen = 10
xlCIDarkBlue = 11
xlCIDarkYellow = 12
xlCIViolet = 13
xlCITeal = 14
xlCIGray25 = 15
xlCIGray50 = 16
xlCIPeriwinkle = 17
xlCIPlum = 18
xlCIIvory = 19
xlCILightTurquoise = 20
xlCIDarkPurple = 21
xlCICoral = 22
xlCIOceanBlue = 23
xlCIIceBlue = 24
'xlCIDarkBlue = 25
'xlCIPink = 26
'xlCIYellow = 27
'xlCITurquoise = 28
'xlCIViolet = 29
'xlCIDarkRed = 30
'xlCITeal = 31
'xlCIBlue = 32
xlCISkyBlue = 33
xlCILightGreen = 35
xlCILightYellow = 36
xlCIPaleBlue = 37
xlCIRose = 38
xlCILavender = 39
xlCITan = 40
xlCILightBlue = 41
xlCIAqua = 42
xlCILime = 43
xlCIGold = 44
xlCILightOrange = 45
xlCIOrange = 46
xlCIBlueGray = 47
xlCIGray40 = 48
xlCIDarkTeal = 49
xlCISeaGreen = 50
xlCIDarkGreen = 51
xlCIBrown = 53
xlCIIndigo = 55
xlCIGray80 = 56
End Enum


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[zong.bing]

Hi, Bob:

In your code, you put Me.Range(WS_RANGE), what does it mean?

Thanks

David

 

[Bob Phillips]

It means the range that is defined in the constant WS_RANGE within the sheet
that the code is contained within (Me).

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)