Thread: conditional cell coloring

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)

"phrodude" <phrodude.2blr13_1154000709.0583@excelforum-nospam.com> wrote in
message news:phrodude.2blr13_1154000709.0583@excelforum-nospam.com...
>
> 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
>
>
> --
> phrodude
> ------------------------------------------------------------------------
> phrodude's Profile:
http://www.excelforum.com/member.php...o&userid=36849
> View this thread: http://www.excelforum.com/showthread...hreadid=565529
>

Hi Bob,

Thanks this looks great it's just that I don''t know that much about 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 breif walk through or guide with what to de next please.

Thanks in advance,


Gavin

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)

"phrodude" <phrodude.2blu1f_1154004610.1911@excelforum-nospam.com> wrote in
message news:phrodude.2blu1f_1154004610.1911@excelforum-nospam.com...
>
> Hi Bob,
>
> Thanks this looks great it's just that I don''t know that much about
> 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 breif
> walk through or guide with what to de next please.
>
> Thanks in advance,
>
>
> Gavin
>
>
> --
> phrodude
> ------------------------------------------------------------------------
> phrodude's Profile:
http://www.excelforum.com/member.php...o&userid=36849
> View this thread: http://www.excelforum.com/showthread...hreadid=565529
>

Hi, Bob:

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

Thanks

David


Bob Phillips wrote:
> 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)
>
> "phrodude" <phrodude.2blu1f_1154004610.1911@excelforum-nospam.com> wrote in
> message news:phrodude.2blu1f_1154004610.1911@excelforum-nospam.com...
> >
> > Hi Bob,
> >
> > Thanks this looks great it's just that I don''t know that much about
> > 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 breif
> > walk through or guide with what to de next please.
> >
> > Thanks in advance,
> >
> >
> > Gavin
> >
> >
> > --
> > phrodude
> > ------------------------------------------------------------------------
> > phrodude's Profile:
> http://www.excelforum.com/member.php...o&userid=36849
> > View this thread: http://www.excelforum.com/showthread...hreadid=565529
> >

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)

<zong.bing@gmail.com> wrote in message
news:1154036877.182314.270380@b28g2000cwb.googlegroups.com...
> Hi, Bob:
>
> In your code, you put Me.Range(WS_RANGE), what does it mean?
>
> Thanks
>
> David
>
>
> Bob Phillips wrote:
> > 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)
> >
> > "phrodude" <phrodude.2blu1f_1154004610.1911@excelforum-nospam.com> wrote
in
> > message news:phrodude.2blu1f_1154004610.1911@excelforum-nospam.com...
> > >
> > > Hi Bob,
> > >
> > > Thanks this looks great it's just that I don''t know that much about
> > > 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 breif
> > > walk through or guide with what to de next please.
> > >
> > > Thanks in advance,
> > >
> > >
> > > Gavin
> > >
> > >
> > > --
> > > phrodude
> >
> ------------------------------------------------------------------------
> > > phrodude's Profile:
> > http://www.excelforum.com/member.php...o&userid=36849
> > > View this thread:
http://www.excelforum.com/showthread...hreadid=565529
> > >
>