-
To trigger the macro, add it to the PivotTableUpdate event of the sheet module. I've added some error checking here in case there are colours that have been filtered out.
[vba]Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim rng As Range
Dim varColour As Variant
Dim intColour As Integer
Dim i As Integer
varColour = Array("Orange", "Yellow")
For i = LBound(varColour) To UBound(varColour)
On Error Resume Next
ActiveSheet.PivotTables("PivotTable1").PivotSelect varColour(i), xlDataAndLabel
' colour may be filtered out of the list, which would cause an error
If Err.Number = 0 Then
For Each rng In Selection
If rng.Column = 1 Or (rng.Column <> 1 And rng.Value <> "") Then
Select Case varColour(i)
Case "Orange": intColour = 45
Case "Yellow": intColour = 6
End Select
rng.Interior.ColorIndex = intColour
rng.Interior.Pattern = xlSolid
If rng.Column = 1 Then
rng.Offset(0, 1).Interior.ColorIndex = intColour
rng.Offset(0, 1).Interior.Pattern = xlSolid
End If
ElseIf rng.Column <> 1 And rng.Value = "" Then
rng.Interior.ColorIndex = xlNone
End If
Next rng
End If
Next i
End Sub[/vba]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules