summing by colour on pivot table

Topic

New Reply

Hi -I am using a pivot table to group large quantities of data. I then add colours on the entries in the pivot to denotes its status. I was wondering if there was anyway I can then total a range within the pivot by colour – i.e. the toal for all the cells turned green = etc. I appreciate I could do it by adjusting the individual data lines but don’t want it to be massively time consuming.

thanks in advance

Reply | Quote

Replies

Robert:

This worked for me.

1. Set a Range Name that covers the Pivot Table Data. In my Code that range is “MyGroup”
2. In a blank cell of the Excel Sheet set the background colour to equal the colour used in the PivotTable to highlight. In my Example it was Cell “B50”
3. Run the below VBA Code. In the below example it puts the total in Cell “C50”

VBA Code

Sub Macro3()
‘
‘ Macro3 Macro

Dim R As Long
Dim Z As Long
‘
R = 0
ActiveSheet.Range(“B50”).Select
Z = Selection.Interior.Color
‘
ActiveSheet.Range(“MyGroup”).Select
‘
For Each mCell In Selection
If mCell.Interior.Color = Z Then
R = R + mCell.Value
End If
Next
ActiveSheet.Range(“C50”).Activate
ActiveCell.Value = R

End Sub

Hope this helps.

DuthieT

Reply | Quote

HI – Thanks for that, that works for a single colour – my next question is how could I get it to work for multiple colours in that same range giving multiple results – basically I have 4 conditions that I use the colours for, i had a quick tinker with your code but my vba is very very limited.

Reply | Quote

Robert

OK the below macro will do 4 Cells so in Cell B50 put the background Colour , B51 the next background colour, B52 the next background colour, and lastly in B53 the final background color. Results will shown in cells C50 to C53

Regards,

Sub Color()
‘
‘ Color Macro
‘
Dim R As Long
Dim Z As Long
‘
R = 0
ActiveSheet.Range(“B50”).Select
Z = Selection.Interior.Color
‘
ActiveSheet.Range(“MyGroup”).Select
‘
For Each MCell In Selection
If MCell.Interior.Color = Z Then
R = R + MCell.Value
End If
Next
ActiveSheet.Range(“C50”).Activate
ActiveCell.Value = R
R = 0
ActiveSheet.Range(“B51”).Select
Z = Selection.Interior.Color
‘
ActiveSheet.Range(“MyGroup”).Select
‘
For Each MCell In Selection
If MCell.Interior.Color = Z Then
R = R + MCell.Value
End If
Next
ActiveSheet.Range(“C51”).Activate
ActiveCell.Value = R
R = 0
ActiveSheet.Range(“B52”).Select
Z = Selection.Interior.Color
‘
ActiveSheet.Range(“MyGroup”).Select
‘
For Each MCell In Selection
If MCell.Interior.Color = Z Then
R = R + MCell.Value
End If
Next
ActiveSheet.Range(“C52”).Activate
ActiveCell.Value = R
R = 0
ActiveSheet.Range(“B53”).Select
Z = Selection.Interior.Color
‘
ActiveSheet.Range(“MyGroup”).Select
‘
For Each MCell In Selection
If MCell.Interior.Color = Z Then
R = R + MCell.Value
End If
Next
ActiveSheet.Range(“C53”).Activate
ActiveCell.Value = R
R = 0
‘
End Sub

Reply | Quote

Robert,

Here is duthiet’s fine code condensed:

Code:

Sub Color()
    Dim x
    x = Array(“B50”, “B51”, “B52”, “B53”)
    For I = LBound(x) To UBound(x)
        Change_Color x(I)
    Next I
End Sub

Public Sub Change_Color(ByVal rng As String)
    R = 0
    Z = ActiveSheet.Range(rng).Interior.Color
    For Each MCell In ActiveSheet.Range(“MyGroup”)

        If MCell.Interior.Color = Z Then
            R = R + MCell.Value
        End If
    Next
    ActiveSheet.Range(rng).Offset(0, 1) = R
End Sub

Reply | Quote