Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jun 2016
    Posts
    5
    Thanks
    3
    Thanked 0 Times in 0 Posts

    summing by colour on pivot table

    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

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    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

  3. The Following User Says Thank You to duthiet For This Useful Post:

    Robert Street (2016-11-02)

  4. #3
    New Lounger
    Join Date
    Jun 2016
    Posts
    5
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Smile Thank you - my next question is

    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.

  5. #4
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    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

  6. The Following User Says Thank You to duthiet For This Useful Post:

    Robert Street (2016-11-04)

  7. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    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
    Last edited by Maudibe; 2016-11-18 at 06:29. Reason: fix wrapping

  8. The Following User Says Thank You to Maudibe For This Useful Post:

    Robert Street (2016-11-07)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •