Hi guys,
I would like to format interior color of all labels of a certain pivotfield. Has anybody idea?
Thanks.
Boris
Printable View
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.
Yes I want to do it in Excel. I am using this code. But I do not want to use a selection methodQuote:
Originally Posted by mebhas
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
Thanks for a try. Insteade of my previous code I have start to use this one:Quote:
Originally Posted by mebhas
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.
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.Quote:
Originally Posted by 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.
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