The 'classic' way of simulating more than 3 conditions in pre-2007 versions
of Excel is to let VBA (a macro) deal with the formatting. Usually the macro
is triggered by some event, such as a change in a cell on a worksheet or even
selecting the worksheet. But the code I'm providing below will let you
choose when to update the format of the cells in a pivot table.

To try it out: open your workbook, press [Alt]+[F11] to get into the VB
Editor. Choose Insert -- Module and copy and paste the code below into the
module presented. Change the name of the worksheet and range of cells to use
in the code. To test it, run it from the [Developer] tab in Excel 2007 or
using Tools -- Macro -- Macros in pre-2007.

Sub SimulateCFormatting()
'for assistance with this code:
'remove spaces & send email & workbook to
'Help From @ JLatham Site. com

Dim ptWorksheet As Worksheet
Dim formatArea As Range
Dim anyCell As Range

'change to name of sheet with Pivot Table on it
Set ptWorksheet = ThisWorkbook.Worksheets("Pivot Tables")
'change to address of cells to be formatted
Set formatArea = ptWorksheet.Range("B5:B21")
'examine each cell within formatArea and
'format it based on its value
'improve performance
Application.ScreenUpdating = False
For Each anyCell In formatArea
'ignore if cell is empty or if
'the cell displays an error condition
If Not IsEmpty(anyCell) And _
Not IsError(anyCell) Then
'reset from any previous condition
With anyCell
.Font.ColorIndex = xlAutomatic
.Interior.ColorIndex = xlAutomatic
End With
Select Case anyCell.Value
Case Is < 25
'set shading to red, font to bold white
anyCell.Interior.ColorIndex = 3 'red
anyCell.Font.ColorIndex = 2 ' white
anyCell.Font.Bold = True
'you can discover other color values
'and settings by simply recording macros
'while you format the cells as you want them
'and examining the recorded macro code.
'or see Dave McRichie's color page:
'http://www.mvps.org/dmcritchie/excel/colors.htm
Case Is < 50
anyCell.Font.ColorIndex = 3 ' red text
Case Is < 75
anyCell.Font.ColorIndex = 6 ' yellow
'hard to see on white background, so
anyCell.Interior.ColorIndex = 1 ' black
Case Else
'value is 75 or greater
'green cell, white text
anyCell.Interior.ColorIndex = 10 ' green
anyCell.Font.ColorIndex = 2 ' white
anyCell.Font.Bold = True
End Select
End If ' end test for empty/error
Next ' end of anyCell loop
'good housekeeping
Set formatArea = Nothing
Set ptWorksheet = Nothing
End Sub



"blmiller2002" wrote:

Hello,

I'm having trouble creating conditional formatting that will work in a pivot
that both 2007 and 2003. I understand that in 2007 you can have more than 3
conditions but 2003 only supports 3. Can anyone give me any suggestions.

Thanks

.