Pivot DataRange BackColor

C

Charly

Hello, sorry I'm just a beginner in VBA, maybe the problem is very
easy...?

I have created a Pivot Table and want to change the background color of
each cell in the DataRange dependent of the containing data
automatically.

How can I do this??

I've already tested this with the function '.PivotSelect "....",
xlDataOnly. But with this function I could not address the cells inside
a loop with indizes - can I?

Thanks for help
 
D

Debra Dalgleish

You can add conditional formatting to the data cells. For example:

'======================
Sub CondFormatPT()
Dim ws As Worksheet
Set ws = Sheets("Pivot")

ws.Cells.FormatConditions.Delete
ws.PivotTables(1).PivotSelect "", xlDataOnly, True
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlGreater, Formula1:="100"
Selection.FormatConditions(1).Interior.ColorIndex = 40
ws.Range("A5").Select

End Sub
'=============================
 
Top