Conditional Formatting Text in Excel from PowerShell

Hopefully a helpful note, as this had me confused for a while…

I wanted to add text-based conditional formatting to an Excel sheet I was creating from PowerShell – so I could colour one of the columns automatically depending on the values.

I used the technique any self-respecting dabbler would: I recorded a macro in Excel VBA and then tried to convert it over.

But no matter what I tried, I couldn’t get the !&$^$^@ FormatConditions.Add function to work.

After debugging the idiot-level mistakes (null variables; debugging without parameters :| ) out of the script, I was left with:

Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))
At C:\Users\tristank\Desktop\excelfmt.ps1:201 char:1
+ $newthing=$ActionColumn.FormatConditions.Add($xlTextString, $cond, $x ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException

Soo… not enough arguments, then?

The VBA reference which comes up first in Bing (and Google for that matter) wasn’t helpful – 4 arguments and I’d tried every combination of arguments I could conceive. The count seemed fine. But no FormatConditions object…

But debugging from the command line (thanks ISE), I tried punching in $ActionColumn.FormatConditions.Add( , and was stunned to see 7 arguments in the tooltip.

I added $null to all of them (natch) – and it worked! (Or at least I had a rule with the right conditions and formatting, but the condition was in the wrong spot).

So, armed with this new knowledge, I found the Excel Interop Object Reference for FormatConditions.Add … and there ya go. 7 arguments.

Object Add( XlFormatConditionType Type, Object Operator, Object Formula1, Object Formula2, Object String, Object TextOperator, Object DateOperator, Object ScopeType )

As I’d already been experimenting by the time I found the documentation, it seems like several bits might be interchangeable. I don’t know what I’m doing; I can’t warrant that this will work under any conditions except my own.

But to save someone else the time, here’s mine:

$ActionColumn = $worksheet.Range("G:G")
$xlTextString = [Microsoft.Office.Interop.Excel.XlFormatConditionType]::xlTextString
$xlContains = [Microsoft.Office.Interop.Excel.XlContainsOperator]::xlContains
$cond = "ACTION"
$newthing=$ActionColumn.FormatConditions.Add($xlTextString, "", $xlContains , $cond, $cond, 0, 0) # hacky dodgy hacky hack / works

$ActionColumn.FormatConditions[$fcs].Font.ThemeColor = [Microsoft.Office.Interop.Excel.XlThemeColor]::xlThemeColorDark1
$ActionColumn.FormatConditions[$fcs].Font.TintAndShade = 0
$ActionColumn.FormatConditions[$fcs].Interior.Color = 255
$ActionColumn.FormatConditions[$fcs].Interior.TintAndShade = 0
$ActionColumn.FormatConditions[$fcs].StopIfTrue = $false;

And yes, I was naughty and used two indexing operators (during my “long script debugging” phase, when I was troubleshooting an uninitialized object and didn’t know it).

Sigh. And hope that helps!