conditional cell coloring

**phrodude** · 07-27-2006, 07:54 AM

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

[email protected] · 07-27-2006, 08:10 AM

phrodude wrote:
> 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

Hi Gav

Are the coloured cells' values calculated with a formula or entered by
a user?

And am I correct in understanding that ONLY the colour is shown and not
the actual value (it's the same colour as the cell)?

Regards

Steve

**phrodude** · 07-27-2006, 08:32 AM

Hi Steve,

Well the data is is entered by me the user into sheet1. This matrix is too large to use for analysis so I'm creating a summary one on sheet 2. the formula for the cells is as follows:

='Balance Scorecard xxxxxxxxxx'!D7

(I've replaced my company name with xxxxxxxxxx)

At the moment I the cells are not colored. I just desire the cell backgrounds to be colored the value/text is not important for viewing at the moment.

So in otherwords I'd like all the higher valued cell to be colored in warm colors (red orange etc..) and all the low valued cells to be colored in cool colors(blue green etc...).

thanks

Gav

Fred · 07-27-2006, 10:15 AM

Hi Gav,

Whilst this may not be an exact fit for what you want, it is code I've
used to set cell colours to indicate the status of projects people are
working on. It is in 2 sections because the version of Excel I use
(97) does not pick up a change when a dropdown selection is used.

Regards
Fred
*
***
*
Private Sub Worksheet_Calculate()

Dim vColor As Long
Dim fColor As Long
Dim vPattern As Long
Dim vPatternColorIndex As Long
Dim cell As Range

fColor = 1
vColor = 15
vPattern = xlSolid
vPatternColorIndex = xlAutomatic

If ActiveSheet.Name = "Demand" Then
For Each cell In Intersect(Range("A3:IV3"), ActiveSheet.UsedRange)
With cell
Select Case LCase(.Text)
Case "r"
vColor = 3
fColor = 2
Case "a"
vColor = 44
' fColor = 1
Case "g"
vColor = 10
fColor = 2
Case "n"
vColor = 7
fColor = 2
Case "d"
vColor = 10
fColor = 2
vPattern = xlLightDown
vPatternColorIndex = 2
Case "p"
vPatternColorIndex = 2
vPattern = xlLightDown
vColor = 41
Case "c"
vColor = 5
fColor = 2
Case "x"
vColor = 1
fColor = 2
Case "h"
vColor = 9
fColor = 2
Case "s"
vColor = 9
fColor = 2
vPattern = xlLightDown
vPatternColorIndex = 2
Case ""
vColor = 15 'xlColorIndexNone
Case Else
vColor = 15 'xlColorIndexNone
fColor = xlColorIndexAutomatic
vPattern = xlSolid
vPatternColorIndex = xlAutomatic
End Select
cell.Interior.ColorIndex = vColor
cell.Font.ColorIndex = fColor
cell.Interior.Pattern = vPattern
cell.Interior.PatternColorIndex = vPatternColorIndex
End With
Next cell
End If
End Sub

*
***
*

Private Sub Worksheet_Change(ByVal Target As Range)
'Fred Newton, 2004-07-27

Dim vColor As Long
Dim fColor As Long
Dim vPattern As Long
Dim vPatternColorIndexIndex As Long
Dim cRange As Range
Dim cell As Range

'***************** check range ****
Set cRange = Intersect(Range("A3:IV3"), (Target(1)))
If cRange Is Nothing Then Exit Sub

fColor = 1
vPattern = xlSolid
vPatternColorIndex = xlAutomatic

For Each cell In cRange
With cell
Select Case LCase(.Text)
Case "r"
vColor = 3
fColor = 2
Case "a"
vColor = 44
Case "g"
vColor = 10
fColor = 2
Case "n"
vColor = 7
fColor = 2
Case "d"
vColor = 10
fColor = 2
vPattern = xlLightDown
vPatternColorIndex = 2
Case "c"
vColor = 5
fColor = 2
Case "x"
vColor = 1
fColor = 2
Case "p"
vPatternColorIndex = 2
vPattern = xlLightDown
vColor = 41
Case "h"
vColor = 9
fColor = 2
Case "s"
vColor = 9
fColor = 2
vPattern = xlLightDown
vPatternColorIndex = 2
Case ""
vColor = 15 'xlColorIndexNone
Case Else
vColor = 15 'xlColorIndexNone
fColor = xlColorIndexAutomatic
vPattern = xlSolid
vPatternColorIndex = xlAutomatic
End Select
cell.Interior.ColorIndex = vColor
cell.Font.ColorIndex = fColor
cell.Interior.Pattern = vPattern
cell.Interior.PatternColorIndex = vPatternColorIndex
End With
Next cell

End Sub

Fred · 07-27-2006, 10:20 AM

Hi Gav,

Whilst this may not be an exact fit for what you want, it is code I've
used to set cell colours to indicate the status of projects people are
working on. It is in 2 sections because the version of Excel I use
(97) does not pick up a change when a dropdown selection is used.

Regards
Fred
*
***
*
Private Sub Worksheet_Calculate()

Dim vColor As Long
Dim fColor As Long
Dim vPattern As Long
Dim vPatternColorIndex As Long
Dim cell As Range

fColor = 1
vColor = 15
vPattern = xlSolid
vPatternColorIndex = xlAutomatic

If ActiveSheet.Name = "Demand" Then
For Each cell In Intersect(Range("A3:IV3"), ActiveSheet.UsedRange)
With cell
Select Case LCase(.Text)
Case "r"
vColor = 3
fColor = 2
Case "a"
vColor = 44
' fColor = 1
Case "g"
vColor = 10
fColor = 2
Case "n"
vColor = 7
fColor = 2
Case "d"
vColor = 10
fColor = 2
vPattern = xlLightDown
vPatternColorIndex = 2
Case "p"
vPatternColorIndex = 2
vPattern = xlLightDown
vColor = 41
Case "c"
vColor = 5
fColor = 2
Case "x"
vColor = 1
fColor = 2
Case "h"
vColor = 9
fColor = 2
Case "s"
vColor = 9
fColor = 2
vPattern = xlLightDown
vPatternColorIndex = 2
Case ""
vColor = 15 'xlColorIndexNone
Case Else
vColor = 15 'xlColorIndexNone
fColor = xlColorIndexAutomatic
vPattern = xlSolid
vPatternColorIndex = xlAutomatic
End Select
cell.Interior.ColorIndex = vColor
cell.Font.ColorIndex = fColor
cell.Interior.Pattern = vPattern
cell.Interior.PatternColorIndex = vPatternColorIndex
End With
Next cell
End If
End Sub

*
***
*

Private Sub Worksheet_Change(ByVal Target As Range)
'Fred Newton, 2004-07-27

Dim vColor As Long
Dim fColor As Long
Dim vPattern As Long
Dim vPatternColorIndexIndex As Long
Dim cRange As Range
Dim cell As Range

'***************** check range ****
Set cRange = Intersect(Range("A3:IV3"), (Target(1)))
If cRange Is Nothing Then Exit Sub

fColor = 1
vPattern = xlSolid
vPatternColorIndex = xlAutomatic

For Each cell In cRange
With cell
Select Case LCase(.Text)
Case "r"
vColor = 3
fColor = 2
Case "a"
vColor = 44
Case "g"
vColor = 10
fColor = 2
Case "n"
vColor = 7
fColor = 2
Case "d"
vColor = 10
fColor = 2
vPattern = xlLightDown
vPatternColorIndex = 2
Case "c"
vColor = 5
fColor = 2
Case "x"
vColor = 1
fColor = 2
Case "p"
vPatternColorIndex = 2
vPattern = xlLightDown
vColor = 41
Case "h"
vColor = 9
fColor = 2
Case "s"
vColor = 9
fColor = 2
vPattern = xlLightDown
vPatternColorIndex = 2
Case ""
vColor = 15 'xlColorIndexNone
Case Else
vColor = 15 'xlColorIndexNone
fColor = xlColorIndexAutomatic
vPattern = xlSolid
vPatternColorIndex = xlAutomatic
End Select
cell.Interior.ColorIndex = vColor
cell.Font.ColorIndex = fColor
cell.Interior.Pattern = vPattern
cell.Interior.PatternColorIndex = vPatternColorIndex
End With
Next cell

End Sub

Fred · 07-27-2006, 10:25 AM

Hi Gav,

Whilst this may not be an exact fit for what you want, it is code I've
used to set cell colours to indicate the status of projects people are
working on. It is in 2 sections because the version of Excel I use
(97) does not pick up a change when a dropdown selection is used.

Regards
Fred
*
***
*
Private Sub Worksheet_Calculate()

Dim vColor As Long
Dim fColor As Long
Dim vPattern As Long
Dim vPatternColorIndex As Long
Dim cell As Range

fColor = 1
vColor = 15
vPattern = xlSolid
vPatternColorIndex = xlAutomatic

If ActiveSheet.Name = "Demand" Then
For Each cell In Intersect(Range("A3:IV3"), ActiveSheet.UsedRange)
With cell
Select Case LCase(.Text)
Case "r"
vColor = 3
fColor = 2
Case "a"
vColor = 44
' fColor = 1
Case "g"
vColor = 10
fColor = 2
Case "n"
vColor = 7
fColor = 2
Case "d"
vColor = 10
fColor = 2
vPattern = xlLightDown
vPatternColorIndex = 2
Case "p"
vPatternColorIndex = 2
vPattern = xlLightDown
vColor = 41
Case "c"
vColor = 5
fColor = 2
Case "x"
vColor = 1
fColor = 2
Case "h"
vColor = 9
fColor = 2
Case "s"
vColor = 9
fColor = 2
vPattern = xlLightDown
vPatternColorIndex = 2
Case ""
vColor = 15 'xlColorIndexNone
Case Else
vColor = 15 'xlColorIndexNone
fColor = xlColorIndexAutomatic
vPattern = xlSolid
vPatternColorIndex = xlAutomatic
End Select
cell.Interior.ColorIndex = vColor
cell.Font.ColorIndex = fColor
cell.Interior.Pattern = vPattern
cell.Interior.PatternColorIndex = vPatternColorIndex
End With
Next cell
End If
End Sub

*
***
*

Private Sub Worksheet_Change(ByVal Target As Range)
'Fred Newton, 2004-07-27

Dim vColor As Long
Dim fColor As Long
Dim vPattern As Long
Dim vPatternColorIndexIndex As Long
Dim cRange As Range
Dim cell As Range

'***************** check range ****
Set cRange = Intersect(Range("A3:IV3"), (Target(1)))
If cRange Is Nothing Then Exit Sub

fColor = 1
vPattern = xlSolid
vPatternColorIndex = xlAutomatic

For Each cell In cRange
With cell
Select Case LCase(.Text)
Case "r"
vColor = 3
fColor = 2
Case "a"
vColor = 44
Case "g"
vColor = 10
fColor = 2
Case "n"
vColor = 7
fColor = 2
Case "d"
vColor = 10
fColor = 2
vPattern = xlLightDown
vPatternColorIndex = 2
Case "c"
vColor = 5
fColor = 2
Case "x"
vColor = 1
fColor = 2
Case "p"
vPatternColorIndex = 2
vPattern = xlLightDown
vColor = 41
Case "h"
vColor = 9
fColor = 2
Case "s"
vColor = 9
fColor = 2
vPattern = xlLightDown
vPatternColorIndex = 2
Case ""
vColor = 15 'xlColorIndexNone
Case Else
vColor = 15 'xlColorIndexNone
fColor = xlColorIndexAutomatic
vPattern = xlSolid
vPatternColorIndex = xlAutomatic
End Select
cell.Interior.ColorIndex = vColor
cell.Font.ColorIndex = fColor
cell.Interior.Pattern = vPattern
cell.Interior.PatternColorIndex = vPatternColorIndex
End With
Next cell

End Sub

[email protected] · 07-27-2006, 10:35 AM

phrodude wrote:
> Hi Steve,
>
> Well the data is is entered by me the user into sheet1. This matrix is
> too large to use for analysis so I'm creating a summary one on sheet 2.
> the formula for the cells is as follows:
>
> ='Balance Scorecard xxxxxxxxxx'!D7
>
> (I've replaced my company name with xxxxxxxxxx)
>
> At the moment I the cells are *not colored*. I just desire the cell
> backgrounds to be colored the value/text is *not important * for
> viewing at the moment.
>
> So in otherwords I'd like all the higher valued cell to be colored in
> warm colors (red orange etc..) and all the low valued cells to be
> colored in cool colors(blue green etc...).
>
> thanks
>
>
> Gav
>
Hi Gav

Try this:

Right-click the tab for Sheet2 > View Code and paste

Private Sub Worksheet_Activate()
Dim c As Range
Dim myRange As Range

Set myRange = Range(Cells(1, 1), Cells(1, 10))

For Each c In myRange
Select Case c.Value
Case Is < 0.1
c.Interior.ColorIndex = 55
c.Font.ColorIndex = 55
Case Is < 0.2
c.Interior.ColorIndex = 5
c.Font.ColorIndex = 5
Case Is < 0.3
c.Interior.ColorIndex = 10
c.Font.ColorIndex = 10
Case Is < 0.4
c.Interior.ColorIndex = 50
c.Font.ColorIndex = 50
Case Is < 0.5
c.Interior.ColorIndex = 43
c.Font.ColorIndex = 43
Case Is < 0.6
c.Interior.ColorIndex = 6
c.Font.ColorIndex = 6
Case Is < 0.7
c.Interior.ColorIndex = 44
c.Font.ColorIndex = 44
Case Is < 0.8
c.Interior.ColorIndex = 45
c.Font.ColorIndex = 45
Case Is < 0.9
c.Interior.ColorIndex = 46
c.Font.ColorIndex = 46
Case Else
c.Interior.ColorIndex = 3
c.Font.ColorIndex = 3
End Select
Next
Set c = Nothing
Set myRange = Nothing
End Sub

This macro will colour the cells A1:J1 depending on the value of each
cell when the sheet is activated. The colours are selected from
Excel's default 56 colour pallette.

Hope this will get you started.

Regards

Steve

**phrodude** · 07-27-2006, 11:26 AM

Hi,

well I got this code and it works great, just one thing this works for cell with raw data in them. My cells data comes from a different sheet with the following formula;

='Balance Scorecard xxxxxxxx'!D3

how do I adjust the following code so that it works for my cells with the above input.

Thanks

Gav

--------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer

If Not Intersect(Target, Range("B2:F2,B4:F16,B19:F72,B74:F97,B99:F110,B112:F120")) Is Nothing Then
Select Case Target
Case 0 To 10
icolor = 6
Case 10 To 20
icolor = 12
Case 20 To 30
icolor = 7
Case 30 To 40
icolor = 53
Case 40 To 50
icolor = 15
Case 50 To 60
icolor = 42
Case Else
'Whatever
End Select

Target.Interior.ColorIndex = icolor
End If

End Sub
--------------------------------------------------------------------------