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

joaolucasd

New Member
Joined
Dec 28, 2020
Messages
5
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):

Captura de Tela (10).png


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:

Captura de Tela (11).png


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!
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
384
Office Version
  1. 2007
Platform
  1. Windows
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.

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,499
Messages
5,625,132
Members
416,074
Latest member
rogie111

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top