Format color in pivot

[bolcskei]

Hi guys,

I would like to format interior color of all labels of a certain pivotfield. Has anybody idea?

Thanks.

Boris

[mebhas]

are u mentioning the pivotfield of excel? in that case u willl have to open excel (and the file) thru code and change the colors of the cells. do a search on the forums about excel automation. u will get more than 20 results.

[bolcskei]

are u mentioning the pivotfield of excel? in that case u willl have to open excel (and the file) thru code and change the colors of the cells. do a search on the forums about excel automation. u will get more than 20 results.

Yes I want to do it in Excel. I am using this code. But I do not want to use a selection method

ActiveSheet.PivotTables("PivotTable1").PivotSelect "" & i & "[All]", xlLabelOnly _
        , True
    With Selection.Interior
     .ColorIndex = 36
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
    End With

[mebhas]

i dont use the pivot tables much, in fact, i havent used it more than twice, so i dont know wxactly how a pivot table works. but there is a way to select a set of cells. if u know the range of cells (supposedly A1:C10) then u can use it this way

With myExcelSheet.Range("A1","C10")
    .ColorIndex = 36
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
End With

[bolcskei]

i dont use the pivot tables much, in fact, i havent used it more than twice, so i dont know wxactly how a pivot table works. but there is a way to select a set of cells. if u know the range of cells (supposedly A1:C10) then u can use it this way

With myExcelSheet.Range("A1","C10")
    .ColorIndex = 36
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
End With

Thanks for a try. Insteade of my previous code I have start to use this one:

Set pvtField = ActiveSheet.PivotTables("PivotTable1").PivotFields(j)
s = pvtField.VisibleItems.Count
ActiveSheet.Range(Cells(7, 1), Cells(7 + s - 1, 1)).Interior.ColorIndex = 36

It is still not what I want, but I already missed the selection function.

[mebhas]

in that case, u'd be better off using the selection method.
personally, i donot have a favourite but i use the range more coz i dont need to work with selections often.

[bolcskei]

in that case, u'd be better off using the selection method.
personally, i donot have a favourite but i use the range more coz i dont need to work with selections often.

I do not like to use selection too. But the first code was my first try for my problem. Now I have better solution, but I am still not satisfied with it. But it work as I wanted.

[mebhas]

well, u may leave this thread open for somebody else (better than me) to reply or u can set this thread as resolved and try to figure something out sometime later and try to finish your work first.

[Hack]

Moved to Office Development

[DKenny]

You can achiueve this by referring to the LabelRange property of each PivotItem in the PivotField in question.
Here's a sample sub where I am changing the ColorIndex for each Label in the pivotField called "Name".

Sub BolcskeiPivotField()
Dim pItem As PivotItem
 
    For Each pItem In ThisWorkbook.Worksheets("Sheet4").PivotTables(1).PivotFields("Name").PivotItems
        pItem.LabelRange.Interior.ColorIndex = 36
    Next pItem
End Sub