excelvbapowershellpowershell-4.0

Powershell Excel Autofilter RGB Colors

I'm currently trying to enter RGB colors in the Excel Autofiter. Does anyone know how this works in Powershell?

$WorkSheet1.Range("A6:AW6").AutoFilter(1,$WorkSheet1.color.rgb(0,255,0), 8)

Thanks

Solution

  • Please try below code

    # get the color you want to filter on and convert it to legacy OLE color
$rgbColor = [System.Drawing.Color]::FromArgb(0,255,0)
$oleColor = [System.Drawing.ColorTranslator]::ToOle($rgbColor)
# operators see https://learn.microsoft.com/en-us/office/vba/api/excel.xlautofilteroperator
$xlFilterCellColor = 8

$WorkSheet1.Range("A6:AW6").AutoFilter(1, $oleColor, $xlFilterCellColor)

    Instead of defining the XlAutoFilterOperator value (8) by hand, you can also start your code with:

    Add-Type -AssemblyName Microsoft.Office.Interop.Excel

    After running this single line, you can use intellisense to get the correct enum values:

    $xlFilterCellColor = [Microsoft.Office.Interop.Excel.XlAutoFilterOperator]::xlFilterCellColor