Format color in pivot

Hi guys,

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

Thanks.

Boris

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.

Quote:

---

Originally Posted by 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.

---

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

VB Code:

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

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

VB Code:

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

Quote:

---

Originally Posted by 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

VB Code:

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:

VB Code:

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.

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.

Quote:

---

Originally Posted by 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.

---

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.

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.

Moved to Office Development

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".

VB Code:

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