Excel Conditional Formatting with PowerShell

I am attempting to use PowerShell (7.4, if it's relevant) with an Excel COM object to programmatically apply conditional formatting to a range.

The only issue I have is in regards to applying conditional formatting to a text string. The specific line with the error is attempting to format any cell with a divide-by-zero error. Also, the cell does not contain a formula, as the results have been pasted back as a value (so the actual cell contents are quite literally #DIV/0! as Excel shows when the formula errors out).

Relevant code sample:

###XlFormatConditionType - parameter 1
$xlCellValue             = 1    #Cell value
$xlExpression            = 2    #Expression
$xlColorScale            = 3    #Color scale
$xlDataBar               = 4    #DataBar
$xlTop10                 = 5    #Top 10 values    
$xlIconSet               = 6    #Icon set
$xlUniqueValues          = 8    #Unique values
$xlTextString            = 9    #Text string
$xlBlanksCondition       = 10   #Blanks condition
$xlTimePeriod            = 11   #Time period
$xlAboveAverageCondition = 12   #Above average condition
$xlNoBlanksCondition     = 13   #No blanks condition
$xlErrorsCondition       = 16   #Errors condition
$xlNoErrorsCondition     = 17   #No errors condition    

###XlFormatConditionOperator - parameter 2
$xlContains              = 0    #needed for textual comparisons, as answered here
$xlBetween               = 1    #Between. Can be used only if two formulas are provided.
$xlNotBetween            = 2    #Not between. Can be used only if two formulas are provided.
$xlEqual                 = 3    #Equal.
$xlNotEqual              = 4    #Not equal.
$xlGreater               = 5    #Greater than.
$xlLess                  = 6    #Less than.
$xlGreaterEqual          = 7    #Greater than or equal to.
$xlLessEqual             = 8    #Less than or equal to.

#TIME TO DO CONDITIONAL FORMATTING!
$rngCondColumn = $shtWorksheet.Range("R2:R" + $shtWorksheet.UsedRange.Rows.Count)
$rngCondColumn.FormatConditions.Add($xlCellValue, $xlLessEqual, 14)
$rngCondColumn.FormatConditions.Add($xlCellValue, $xlBetween, 14, 64)
$rngCondColumn.FormatConditions.Add($xlCellValue, $xlGreaterEqual, 64)
$rngCondColumn.FormatConditions.Add($xlTextString, $xlEqual, "#DIV/0!")
    
$colorBad = 3 #red
$colorCaution = 6 #yellow
$colorGood = 4 #lime

$rngCondColumn.FormatConditions.Item(1).Interior.ColorIndex = $colorBad
$rngCondColumn.FormatConditions.Item(2).Interior.ColorIndex = $colorCaution
$rngCondColumn.FormatConditions.Item(3).Interior.ColorIndex = $colorGood
$rngCondColumn.FormatConditions.Item(4).Interior.ColorIndex = $colorGood

This line is the only one that doesn't behave as expected:

$rngCondColumn.FormatConditions.Add($xlTextString, $xlEqual, "#DIV/0!")

... and it gives the error: OperationStopped: Parameter not optional. (0x8002000F (DISP_E_PARAMNOTOPTIONAL))

So, I also tried this change:

$rngCondColumn.FormatConditions.Add($xlCellValue, $xlEqual, "#DIV/0!")

... which doesn't error-out, but also doesn't apply the conditional formatting change (whereas the other three FormatConditions function exactly as expected).

It's clear that checking for a string (of any kind), in the conditional formatting through PowerShell isn't being called properly on my side (the parameter error above).

Setting up the conditional format manually does work as it should, but only when the cell contents are changed from #DIV/0! to something more akin to a regular string (say, string, in place of #DIV/0!).

However, I'd like to keep the #DIV/0!, and can't determine if there's some sort of escape character I need to account for in this format condition?

The FormatConditions method and syntax are here.

Solution

Unfortunately, the Microsoft documentation for the FormatConditions.Add() method only sort-of describes four parameters and the examples are always only about adding parameters for format condition type xlCellValue, which needs just the first 3 parameters.

However, based on this answer, it seems that the Add() method can take 8 parameters and if in your case you need to use the xlTextString format condition type, you will need to specify them all.

$xlTextString = 9
$xlContains   = 0

$rngCondColumn.FormatConditions.Add($xlTextString, 
                                    [Type]::Missing, 
                                    [Type]::Missing, 
                                    [Type]::Missing, 
                                    "#DIV/0!", 
                                    $xlContains, 
                                    [Type]::Missing, 
                                    [Type]::Missing)

^{The optional Variant types translate to [Type]::Missing in PowerShell.}

I would suggest to put Add-Type -AssemblyName Microsoft.Office.Interop.Excel on top of your code.
This helps with finding the correct values for Excel constants and if you run that line first, you will also have the benefit of intellisense while coding

$rngCondColumn.FormatConditions.Add([Microsoft.Office.Interop.Excel.XlFormatConditionType]::xlTextString, 
                                    [Type]::Missing, 
                                    [Type]::Missing, 
                                    [Type]::Missing, 
                                    "#DIV/0!", 
                                    [Microsoft.Office.Interop.Excel.XlContainsOperator]::xlContains, 
                                    [Type]::Missing, 
                                    [Type]::Missing)