Adjust conditional Format

[k3vsmith]

Im using conditional formatting to highlight a field in a pivot table. This works in that any that are Yes are highlighted in RED. But I also want to have any that are #N/A highlighted in Yellow. Do I do this within the same With Statement? With its own with? Which I tried and it changes the Yes to be highlighted in Yellow. Can you provide syntax to handle #N/A. Here is what I have that is working for Yes to highlight Red:

'define column header constants
Const cnDateMissing = "ae1"

'dimension module level variables
Dim pvT As PivotTable
Dim pvF As PivotField

    Set pvT = ActiveSheet.PivotTables(1)

Set pvF = pvT.PivotFields(wsInput.Range(cnDateMissing).Value)
    With pvF.DataRange

        .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""Yes"""
        .FormatConditions(1).Interior.Color = 255
               
    End With

 

[k3vsmith]

Also - wsInput is declared as worksheet

 

[Chihiro]

You should be able to do it in same "With" statement.

However, #N/A error is annoying to work with.
I'd recommend applying PivotTable Options ->Format -> For error values show:. And choosing something like "N/A".

So you can simply use Formula1:="=""N/A"""

Not tested, but code would be something like...

Set pvF = pvT.PivotFields(wsInput.Range(cnDateMissing).Value)
    With pvF.DataRange

        .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""Yes"""
        .FormatConditions(1).Interior.Color = 255
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""N/A"""
        .FormatConditions(2).Interior.Color = 255 'Change color
            
    End With

 

[k3vsmith]

That works. Thanks!