Color Coding variable Pivot Row Label Interior

Closed
BwanaBill Posts 2 Registration date Sunday February 22, 2015 Status Member Last seen March 5, 2015 - Feb 23, 2015 at 04:12 PM
BwanaBill Posts 2 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

BwanaBill Posts 2 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