VBA Excel 2016
I have a pivot table of operating expenses and I want it to change to red any cells that have YoY growth >.3 or <-.3. For that I need the macro to automatically apply these two conditions:
(value in column "2018 Ave. PSF" - value in column "2017 Ave. PSF")/(value in column "2017 Ave. PSF")>.3
(value in column "2018 Ave. PSF" - value in column "2017 Ave. PSF")/(value in column "2017 Ave. PSF")<-.3
I have columns that already make those calculations, but having those columns activated disrupts my pivot charts.
This is my (very) wrong VBA code:
Sub PercentFormat( . . . )
'Set Variables
Dim pf as PivotField
Dim pt as PivotTable
Application.ScreenUpdating = False
For Each pf In pt.DataFields
If Abs((Range("2018 Ave PSF").Value - Range("2017 Ave PSF").Value) / Range("2017 Ave PSF").Value) > 0.3 Then
Cell.Interior.Color = vbRed
Cell.Font.Color = vbWhite
End If
If Abs((Range("2018 Ave PSF").Value - Range("2017 Ave PSF").Value) / Range(column with year 1).Value) < -0.3 Then
Cell.Interior.Color = vbRed
Cell.Font.Color = vbWhite
End If Next
Application.ScreenUpdating = True
End Sub
Bookmarks