Prev: Time entry help
Next: Converting a letter to a numeric value
From: blmiller2002 on 15 Apr 2010 11:31
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

From: JLatham on 16 Apr 2010 09:56
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
>
> .
>
From: blmiller2002 via OfficeKB.com on 16 Apr 2010 11:07
J,

I appreciate your help, this looks like exactly what we need. One more
question, the people who are going to be looking at this report might find
the need to add or subtract fields from the pivot. Do you know of a way to
insert this formatting into the sheet and give it the functionally to be able
to keep the formatting if things are re-arranged?

Thanks

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/201004/1

From: JLatham on 16 Apr 2010 22:12
If the pivot table(s) is/are on a sheet separate from the data, it's fairly
easy - we'd move the code into that sheet's _Activate() event processor, then
it would update the format when the sheet is selected.

The problem becomes during the adding/deleting of fields in the PT, and
having the range to look at hard coded. Let me play around some and see if I
can't come up with something.

If we knew that the PT was always going to start on the same row, that would
help, and if we could simply reset the formatting of the entire column to
xlAutomatic, that would make it almost a piece of cake.


"blmiller2002 via OfficeKB.com" wrote:

> J,
>
> I appreciate your help, this looks like exactly what we need. One more
> question, the people who are going to be looking at this report might find
> the need to add or subtract fields from the pivot. Do you know of a way to
> insert this formatting into the sheet and give it the functionally to be able
> to keep the formatting if things are re-arranged?
>
> Thanks
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.aspx/ms-excel/201004/1
>
> .
>
 | 
Pages: 1
Prev: Time entry help
Next: Converting a letter to a numeric value