Previous Topic Next Topic

Color Coding variable Pivot Row Label Interior

Closed
BwanaBill Posts 3 Registration date Sunday February 22, 2015 Status Member Last seen March 5, 2015 - Feb 23, 2015 at 04:12 PM
BwanaBill Posts 3 Registration date Sunday February 22, 2015 Status Member Last seen March 5, 2015 - Mar 5, 2015 at 06:30 PM
Hello All

I have a coding problem I have tried numerous ways to solve with no success.
I have a macro generating a pivot table for weekly data that constantly changes, hence rows and columns are variable. My issue is that the row labels from E5 to the right hand edge of the table need to be colour coded according to the text contained therein, per below. The row label text may or may not appear from week to week, hence I need to perform a test such as shown. There may be a better way.

Please, if anyone is able to assist, I'd be really grateful.
Appreciate any help
Thanks! 

 '----------------------------------------------------------------------
    'Apply Colours to Row Labels
          
    Dim LC As Long
    Dim MyRng As PivotCell
    
    Dim cell As PivotCell
    
    LC = Range("E5").CurrentRegion.Columns.Count

    '--------------------------------------------------------------
    'PROCESS BREAKS HERE
    
    MyRng = Range("E5:E" & LC - 1)
       
    For Each cell In MyRng
    '---------------------------------------------------------------
    Select Case cell.Value
    
    Case Is = "B50", "B60", "B70", "B30", "B90", "BMO", "BO1", "BO2"
        cell.Interior.Color = 5287936


    Case Is = "B61", "B65", "B71", "B15", "B31", "B48"
        cell.Interior.Color = 10498160


    Case Is = "B64", "B10", "B20", "B15", "B25", "B32", "B37", "B38", "B42"
        cell.Interior.Color = 49407


    Case Is = "B62", "B63", "B75", "B35", "B39", "B34", "B33", "B41", "B40", "B36", "B46"
        cell.Interior.Color = 13434879

    Case Is = "Grand Total"
        cell.Interior.Color = xlNone
        
    Case Else
        cell.Interior.Color = 65535

    End Select
    
    Next
'------------------------------------------------------------------
Related:

1 response

Answer 1 / 1
BwanaBill Posts 3 Registration date Sunday February 22, 2015 Status Member Last seen March 5, 2015
Mar 5, 2015 at 06:30 PM
I found a clunky way of getting round this particular problem!
This code works, but I am sure there is a better way.
I would really appreciate some learned input from you experts out there! 


'    'Apply Colours to Row Labels

    Range("E5").Select
    Range(Selection, Selection.End(xlToRight)).Select
    
    Dim myRng As Range
    Set myRng = Selection

    Dim cell As Object

    For Each cell In myRng

    Select Case cell.Value

        Case Is = "B50", "B60", "B70", "B30", "B90"
            cell.Interior.Color = 5296274

        Case Is = "BMO", "BO1", "BO2", "MGR"
            cell.Interior.Color = 5296274


 '       Case Is = "B61", "B65", "B71", "B15", "B31", "B48"
 '           cell.Interior.Color = 10498160

        Case Is = "Grand Total"
            cell.Interior.Color = xlNone

        Case Else
            cell.Interior.Color = 65535

    End Select

    Next
0

Similar discussions

Connecting IO Box to Switch without Patch Pan
Vikas Yadav -
chandrakant -

2 responses
Ig stories from close friends to public
Darren -
zara -

31 responses