Conditional Formatting

Conditional Formatting

Didn't find your answer?

Ideally what I need is, for the number 1, shade the cell red, for example. Number 2 blue, Number 3 green, Number 4 yellow and so on.

I also would like it to work within a pivot table.

Any help appreciated, cheers.
Ian Wingfield

Replies (9)

Please login or register to join the discussion.

avatar
By 0456459
26th Aug 2005 08:39

Thanks
Alan, It's very useful. Thanks a lot.

Thanks (0)
avatar
By Wingy
23rd Sep 2003 14:56

20
Probably a maximum of twenty colours

Thanks (0)
avatar
By ACDWebb
23rd Sep 2003 14:10

How many colours do you want to use?
what range will they fall in? single digits or greater?

In 97 the limit is 3 conditions so you may well need to create a macro / function to be fired by sheet calculation / change

Thanks (0)
avatar
By tomgorham12
23rd Sep 2003 13:56

Conditional Formatting
Easy enough to do from the Format, Conditional Formatting... menu.

Not sure if this would work in Pivot tables though

Thanks (0)
avatar
By ACDWebb
24th Sep 2003 14:36

Pivot table
Just checked and it works OK

In VBA on the sheet with your pivottable on put the following:

Private Sub Worksheet_Calculate()
ColourCells
End Sub

Assuming that you have adjusted the code to a macro called ColourCells then when you refresh the table the cells get recoloured as necessary

Thanks (0)
avatar
By ACDWebb
24th Sep 2003 14:21

...continued
If you want it to work on just one sheet but update the whole sheet and any formulae as you go along then you could put the revised macro (or call it) into the Private Sub Worksheet_Change(ByVal Target As Excel.Range) secttion of the specific sheet. However in a large sheet it could slow down the worksheet considerably as the macro would check and change as necessary all cells in that sheet everytime that you made a change.

If you were only worried about colouring a particular block of cells, not the whole sheet you could speed things up by changing ActiveSheet.UsedRange.Cells to ActiveSheet.Range("Cell ref ie A1:B5).Cells

I doubt that it will work on your pivot table though, but have not checked that. You might hit problems with Selected cells in the table.

Thanks (0)
avatar
By ACDWebb
24th Sep 2003 09:03

This will do 10
but you could expand it for more as required.

In Excel press Alt + F11 to open the VBA editor
then paste the following into ThisWorkbook

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Dim tCell As Object
Dim fColour 'Font colour
Dim bColour 'Cell colour
Const cBlack As Byte = 1
Const cWhite As Byte = 2
Const cRed As Byte = 3
Const cGreen As Byte = 4
Const cBlue As Byte = 5
Const cYellow As Byte = 6
Const cPurple As Byte = 7
Const cLBlue As Byte = 8
Const cDRed As Byte = 9
Const cDGreen As Byte = 10
Const cDBlue As Byte = 11

Set tCell = Worksheets(Sh.Name).Range(Target.Address)
If Not IsNumeric(tCell.Value) Then GoTo NextOne
Select Case Target.Value
Case 0 To 9
fColour = cBlack
bColour = cWhite
Case 10 To 19
fColour = cWhite
bColour = cBlack
Case 20 To 29
fColour = cBlack
bColour = cRed
Case 30 To 39
fColour = cBlack
bColour = cGreen
Case 40 To 49
fColour = cWhite
bColour = cBlue
Case 50 To 59
fColour = cBlack
bColour = cYellow
Case 60 To 69
fColour = cWhite
bColour = cPurple
Case 70 To 79
fColour = cBlack
bColour = cLBlue
Case 80 To 89
fColour = cWhite
bColour = cDRed
Case 90 To 99
fColour = cWhite
bColour = cDGreen
Case Else
fColour = cWhite
bColour = cDBlue
End Select
tCell.Font.ColorIndex = fColour
tCell.Interior.ColorIndex = bColour
tCell.Interior.Pattern = xlSolid
NextOne:
End Sub

Each time you type in a number this macro will be run and the font & colour cell that you are on will be set depending on where the number falls in the above ranges.

Alternatively add a VBA module and a Sub called ColourCells then paste the above into the Sub but change it as follows:

(i) Change Dim tCell As Object to Dim tCell
(ii) Delete the line Set tCell = Worksheets(Sh.Name).Range(Target.Address)
(iii)Add the following line For Each tCell In ActiveSheet.UsedRange.Cells
before If Not IsNumeric(tCell.Value) Then GoTo NextOne
(iv) add the line Next tCell after the line NextOne: and then Run the macro on the sheet you want to be coloured.

I doubt that it will work on your pivot table though

Sorry have edited this a couple of times to fix/correct a few things. Think I have got them all

Thanks (0)
avatar
By ACDWebb
25th Sep 2003 14:46

Ian - Try this
Sub ColourCells()
Dim tCell As Object
Const cBlack As Byte = 1
Const cWhite As Byte = 2
Const cRed As Byte = 3
Const cGreen As Byte = 4
Const cBlue As Byte = 5
Const cYellow As Byte = 6
Const cPurple As Byte = 7
Const cLBlue As Byte = 8
Const cDRed As Byte = 9
Const cDGreen As Byte = 10
Const cDBlue As Byte = 11
Dim fColour
Dim bColour
For Each tCell In ActiveSheet.UsedRange.Cells
If Not IsNumeric(tCell.Value) Then GoTo NextOne
Select Case tCell.Value
Case 0 To 9
fColour = cBlack
bColour = cWhite
Case 10 To 19
fColour = cWhite
bColour = cBlack
Case 20 To 29
fColour = cBlack
bColour = cRed
Case 30 To 39
fColour = cBlack
bColour = cGreen
Case 40 To 49
fColour = cWhite
bColour = cBlue
Case 50 To 59
fColour = cBlack
bColour = cYellow
Case 60 To 69
fColour = cWhite
bColour = cPurple
Case 70 To 79
fColour = cBlack
bColour = cLBlue
Case 80 To 89
fColour = cWhite
bColour = cDRed
Case 90 To 99
fColour = cWhite
bColour = cDGreen
Case Else
fColour = cWhite
bColour = cDBlue
End Select
tCell.Font.ColorIndex = fColour
tCell.Interior.ColorIndex = bColour
tCell.Interior.Pattern = xlSolid
NextOne:
Next tCell
End Sub

That should do it I think. Your problems are that I forgot to tell you to take the code between Public Sub and End Sub so as you have it VBA thinks that you are trying to start a second macro Public Sub Workbook_..... without having finishedthe other Public Sub ColourCells(), and you were testing the value Select Case Target.Value (when Target as a variable does not exist) and if you had got past that had End Select tCell which is right, but only if you start with Select Case tCell.Value.

The pivottable that I tested on is on Sheet3 and in the VBA editor under Microsoft Excel Objects on Sheet3(Sheet3) I have :

Private Sub Worksheet_Calculate()
ColourCells
End Sub

So it looks as if you are OK on that.

You will hopefully have gathered from the code that what the macro does is look at the entry in each cell on the worksheet.

If it is not a number then the cell is ignored.

If however it is a number and falls between:
(a)1 and 9 then the Cell Font colour is set to Black and the Cell colour to white.
(b)10 and 19 then the Cell Font colour is set to white and the Cell colour to black.
(c) onwards - etc.

So you will need to set the number ranges to the distinct range of numbers that you want, extend the list for the additional number ranges that you want, adding to the colours that you want in the Const entries in the declarations at the top of the macro. I have set those to make it easier to work out what colours you are setting.

If you do not know the other colour codes then put the cursor on one of the ColorIndex entries and press F1. Theat should give you a Help page with the colour palette and relevant numbers.

See how you get on with that!! Good luck

Thanks (0)
avatar
By Wingy
25th Sep 2003 11:41

Struggling with VBA
Alan, I appreciate the time and effort, however I'm not any good with VBA and am struggling to get things to work. Let me see if I can explain where I am.

I have a Module1 within Modules, and the code contained in (General) Colourcells (i.e. the dialog boxes at the top is as follows:

Public Sub ColourCells()
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Dim tCell
Dim fColour 'Font colour
Dim bColour 'Cell colour
Const cBlack As Byte = 1
Const cWhite As Byte = 2
Const cRed As Byte = 3
Const cGreen As Byte = 4
Const cBlue As Byte = 5
Const cYellow As Byte = 6
Const cPurple As Byte = 7
Const cLBlue As Byte = 8
Const cDRed As Byte = 9
Const cDGreen As Byte = 10
Const cDBlue As Byte = 11

For Each tCell In ActiveSheet.UsedRange.Cells
If Not IsNumeric(tCell.Value) Then GoTo NextOne
Select Case Target.Value
Case 0 To 9
fColour = cBlack
bColour = cWhite
Case 10 To 19
fColour = cWhite
bColour = cBlack
Case 20 To 29
fColour = cBlack
bColour = cRed
Case 30 To 39
fColour = cBlack
bColour = cGreen
Case 40 To 49
fColour = cWhite
bColour = cBlue
Case 50 To 59
fColour = cBlack
bColour = cYellow
Case 60 To 69
fColour = cWhite
bColour = cPurple
Case 70 To 79
fColour = cBlack
bColour = cLBlue
Case 80 To 89
fColour = cWhite
bColour = cDRed
Case 90 To 99
fColour = cWhite
bColour = cDGreen
Case Else
fColour = cWhite
bColour = cDBlue
End Select
tCell.Font.ColorIndex = fColour
tCell.Interior.ColorIndex = bColour
tCell.Interior.Pattern = xlSolid
NextOne:
Next tCell
End Sub


End Sub

And withing the worksheet I have the following code

Private Sub Worksheet_Calculate()
ColourCells
End Sub

When I refresh my pivot table I get - compile error expected end sub and the Public Sub ColourCells is highlighted.

Any clue where I am going wrong.

cheers

Thanks (0)