Conditional Formatting Pivot Table based on Dynamic Column Headers

Question

thanks for giving a newbie in VBA a little help and guidance.
I am trying to program a macro for conditional formatting in a pivot table(s).

Goal is to find two columns by headers (as their location might change) and apply a conditional formula to one of them. (highlight outlier cells in red)

Headers are in Row 3
Columns starting with A

Example below:

I want to find the column that has Origin Airport,
then find the column that has Origin Handling,
create a formula the has 2 conditions to compare,e.g. B4 with B5 (match) and D4 with D5 (match) and then highlight any values in Origin Handling (column D) that are not in line, where D4 <> D5.
The following formula I used to test for consistency "=(AND($B4=$B5,D4<>D5)"

! [screenshot of the table] (https://i.stack.imgur.com/fhNog.jpg)

I have gone through various posts here on SO and put the codes together that i think are applicable. Being a newbie however I am not getting the correct flow, was trying segment by segment but hit a wall when trying to find a code that provides column Letter by find: Header and then creates a formula. Below code sequence is incomplete.

    Sub Origin_Charges
        ' Review origin charges based on Consistency
        With ActiveSheet.PivotTables("Origin") 'focuses on the Pivot Table I named Origin'
            Dim ws as worksheet
            Dim rngDateHeader As Range
            Dim rngHeaders As Range
        End with

        Set rngHeaders = Range("3:3") 'Looks in entire first row; adjust as needed.
        Set rngDateHeader = rngHeaders.Find("Origin Airport")
            If rngDateHeader is Nothing then "No Origin Airport Column Found"

        Set rngHeaders = Range("3:3") 'Looks in entire first row; adjust as needed.
        Set rngDateHeader = rngHeaders.Find("Origin Handling")
            If rngDateHeader is Nothing then "No Origin Handling Column Found"

        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=AND($B4=$B5,D4<>D5)" 'Formula right now is fixed but want it to adjust based on what column Origin Airport is in'
        Selection.FormatConditions(Selection.
        FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
        End With

        With Sheets("Origin")
           .Range("D4").AutoFill .Range("D4:D" & .Cells(.Rows.count,"C")
           .End(xlUp).row)
        End With
    End Sub

Any help is greatly appreciated.

Which sentence in the above is failing? See https://stackoverflow.com/help/minimal-reproducible-example — donPablo, Jul 16 '19 at 03:57
`rngDateHeader.EntireColumn.Cells(4).Address(false, false)` for example would give you "B4" if the header was in ColB — Tim Williams, Jul 16 '19 at 05:19
I am missing the logical step from finding the two headers (locations) and then based on the column to create the formula. basically the step after Find("Origin Handling") to programming the formula. — H BG, Jul 16 '19 at 12:35
@TimWilliams thanks Tim, how would I be able to have the formula capture B4 in your example? — H BG, Jul 16 '19 at 14:03
found this code: [link] (https://stackoverflow.com/questions/32014492/excel-vba-that-searches-by-header-name-not-column) He is using a vlookup as part of his formula. Is there a way to incorporate the rngeheader.find / Tim's suggestion as part of the formula? — H BG, Jul 16 '19 at 14:05

score 1 · Accepted Answer · answered Jul 16 '19 at 15:00

You can do something like this:

Dim c As Range, sht As Worksheet, f as string

Set sht = Activesheet

Set c = sht.Cells(4, rngDateHeader.Column) 'eg B4

f = "=AND(" & c.address(false, false) & "=" & _
     c.offset(1,0).address(false, false) & ",D4<>D5)"  '<< use formula f in your CF

Conditional Formatting Pivot Table based on Dynamic Column Headers

1 Answers1