Macro to Modify Pivot Table for Color and Bold

[Cheryl B.]

I've spent some time looking through the help menus and this queue but
haven't found a solution ...

I have a pivot table that will change as data is refreshed. I need to write
a macro to find, change color and bold rows that contain the word 'Total'
after the pivot table is created.

Any ideas / suggestions are appreciated!
Cheryl B.

[Bernie Deitrick]

Cheryl,

Try the macro below. Change the line

Set myFirstCell = Range("A7")

to reflect a cell actually within your pivot table. If the sheet with the pivot table is not the
active sheet at the time the macro is run, then you will need to change it to

Set myFirstCell = Worksheets("Pivot Table Sheet").Range("A7")

HTH,
Bernie
MS Excel MVP

Sub ColorNBoldTotals()
Dim c As Range
Dim myFindString As String
Dim firstAddress As String
Dim myFirstCell As Range

Set myFirstCell = Range("A7")
myFindString = "total"

With myFirstCell.CurrentRegion

Set c = .Find(myFindString, LookIn:=xlValues, lookAt:=xlPart)

If Not c Is Nothing Then
With Intersect(c.EntireRow, .Cells)
.Font.Bold = True
With .Interior
.ColorIndex = 6
End With
End With
firstAddress = c.Address
Else:
MsgBox "Not Found"
End
End If

Set c = .FindNext(c)
If Not c Is Nothing And c.Address <> firstAddress Then
Do
With Intersect(c.EntireRow, .Cells)
.Font.Bold = True
With .Interior
.ColorIndex = 6
End With
End With

Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With

End Sub


"Cheryl B." <CherylB@discussions.microsoft.com> wrote in message
news:A660689A-451F-4310-BF7C-E83C4CD0F38F@microsoft.com...
> I've spent some time looking through the help menus and this queue but
> haven't found a solution ...
>
> I have a pivot table that will change as data is refreshed. I need to write
> a macro to find, change color and bold rows that contain the word 'Total'
> after the pivot table is created.
>
> Any ideas / suggestions are appreciated!
> Cheryl B.