conditional cell coloring

  • Thread starter Thread starter phrodude
  • Start date Start date
P

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
 
'-----------------------------------------------------------------
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)
 
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
 
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)
 
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)
 
Back
Top