Excel/VBA - Fill color of a cell in pivot table according to column value in source table

[joaolucasd]

Hello!

I want to make a pivot table to control clients' payments. This pivot table has as source a table in another sheet, like this (I made a simpler table just to use as example):

I want the pivot table to have these elements:

Row1 - Client
Row2 - Type of income
Column - Month
Values - Value

The reason for this post is that I want each cell in the pivot table to have a color according to the "Control" column of the source table. The values that are marked as "paid" I want to have a green fill color and the ones marked as "not paid" I want it to be red. I made, as an example, a pivot table and colored the cells manually:

But I want it to make this automatically, since the original table has a lot of rows and I keep adding them daily. I tried messing around with Conditional Formatting, but without success. Is there a way I can do this using VBA or even with a complex formula in conditional formatting?

Thank you in advance for taking the time to read this!

 

[EXCEL MAX]

Hello Joaolucasd,
here is VBA code that can can highlight "Paid" values to green, and "Not paid" to red.
It's not tested on the big size data.
It's on you.

Sub HiglightPivotValues()

    Dim varWS As Worksheet
    Dim varNRows As Long, varNColumns As Long
    Dim varRange1 As Range, varRange2 As Range, varRange3 As Range
    Dim varClientPivot
    Dim varSwich As Byte

    Application.ScreenUpdating = False
    Set varWS = Sheets("PivotTable")
    varWS.Activate
    ActiveWorkbook.RefreshAll
    varWS.PivotTables("PivotTable1").DisplayFieldCaptions = False
    varWS.PivotTables("PivotTable1").RowGrand = False
    varWS.PivotTables("PivotTable1").ColumnGrand = False
    varWS.PivotTables("PivotTable1").PivotFields("Control").Orientation = xlRowField
    varNRows = varWS.UsedRange.Rows.Count + 2
    varNColumns = varWS.UsedRange.Columns.Count
    varWS.UsedRange.Interior.Color = RGB(255, 255, 255)
    varWS.UsedRange.Font.Color = RGB(0, 0, 0)
    Set varRange2 = varWS.Range("A5:A" & varNRows)
    For Each varRange1 In varRange2
        If varRange1.PivotField.Name = "Client" Or _
            varRange1.PivotField.Name = "Type of income" Then
            If varSwich = 0 Then
               varSwich = 1
               varWS.Range(Cells(varRange1.Row, 1), Cells(varRange1.Row, varNColumns)) _
                    .Interior.Color = RGB(217, 217, 217)
            Else
                 varSwich = 0
            End If
            If varRange1.PivotField.Name = "Client" Then _
                varWS.Range(Cells(varRange1.Row, 2), _
                    Cells(varRange1.Row, varNColumns)) _
                    .Font.Color = _
                varWS.Range(Cells(varRange1.Row, 2), _
                    Cells(varRange1.Row, varNColumns)) _
                    .Interior.Color
        End If
        If varRange1.Value = "Paid" Then
           Set varRange3 = varWS.Range(Cells(varRange1.Row, 1), _
                Cells(varRange1.Row, varWS.UsedRange.Columns.Count))
           Set varClientPivot = varRange3.Find("*", Cells(varRange1.Row, 1))
           varClientPivot.Offset(-1, 0).Interior.Color = RGB(198, 224, 180)
        End If
        If varRange1.Value = "Not paid" Then
           Set varRange3 = varWS.Range(Cells(varRange1.Row, 1), _
                Cells(varRange1.Row, varWS.UsedRange.Columns.Count))
           Set varClientPivot = varRange3.Find("*", Cells(varRange1.Row, 2))
           varClientPivot.Offset(-1, 0).Interior.Color = RGB(248, 203, 173)
        End If
    Next
    varWS.PivotTables("PivotTable1").PivotFields("Control").Orientation = xlHidden
    varWS.Range(Cells(3, 1), Cells(4, varNColumns)) _
                .Interior.Color = RGB(217, 225, 242)
    Application.ScreenUpdating = True
   
End Sub

 

[joaolucasd]

Thank you for your answer! I'll try it out and give you a feedback!