Color Coding variable Pivot Row Label Interior

[Closed]
Report
Posts
3
Registration date
Sunday February 22, 2015
Status
Member
Last seen
March 5, 2015
-
Posts
3
Registration date
Sunday February 22, 2015
Status
Member
Last seen
March 5, 2015
-
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
'------------------------------------------------------------------

1 reply

Posts
3
Registration date
Sunday February 22, 2015
Status
Member
Last seen
March 5, 2015

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