New-ConditionalText.ps1

function New-ConditionalText {
    <#
      .SYNOPSIS
        Creates an object which describes a conditional formatting rule for single valued rules.
      .DESCRIPTION
        Some Conditional formatting rules don't apply styles to a cell (IconSets and Databars).
        Some take two parameters (Between).
        Some take none (ThisWeek , containsErrors, AboveAverage etc).
        The others take a single parameter (Top, BottomPercent, GreaterThan, Contains etc).
        This command  creates an object to describe the last two categories, which can then be passed to Export-Excel.
      .PARAMETER Range
        The range of cells that the conditional format applies to; if none is specified the range will be apply to all the data in the sheet.
      .PARAMETER ConditionalType
        One of the supported rules; by default "ContainsText" is selected.
      .PARAMETER Text
        The text (or other value) to use in the rule. Not that Equals, GreaterThan/LessThan rules require text to wrapped in double quotes.
      .PARAMETER ConditionalTextColor
        The font color for the cell - by default: Dark red.
      .PARAMETER BackgroundColor
        The fill color for the cell - by default: Light pink.
      .PARAMETER PatternType
        The Background pattern for the cell - by default: Solid
      .EXAMPLE
        $ct = New-ConditionalText -Text  'Ferrari'
        Export-Excel -ExcelPackage $excel -ConditionalTest $ct -show

        The first line creates a definition object which will highlight the word "Ferrari" in any cell.
        and the second uses Export-Excel with an open package to apply the format and save and open the file.
      .EXAMPLE
        $ct  = New-ConditionalText -Text "Ferrari"
        $ct2 = New-ConditionalText -Range $worksheet.Names["FinishPosition"].Address -ConditionalType LessThanOrEqual -Text 3 -ConditionalTextColor Red -BackgroundColor White
        Export-Excel -ExcelPackage $excel -ConditionalText $ct,$ct2 -show

        This builds on the previous example, and specifies a condition of <=3 with a format of Red text on a white background; this applies to a named range "Finish Position"
        the range could be written "C:C" to specify a named column, or "C2:C102" to specify certain cells in the column.
    #>
    [cmdletbinding()]
    param(
        #[Parameter(Mandatory=$true)]
        [Alias("ConditionValue")]
        $Text,
        [Alias("ForeGroundColor")]
        [System.Drawing.Color]$ConditionalTextColor="DarkRed",
        [System.Drawing.Color]$BackgroundColor="LightPink",
        [String]$Range,
        [OfficeOpenXml.Style.ExcelFillStyle]$PatternType=[OfficeOpenXml.Style.ExcelFillStyle]::Solid,
        [ValidateSet(
            "LessThan",        "LessThanOrEqual",      "GreaterThan",    "GreaterThanOrEqual",
            "Equal",           "NotEqual",
            "Top",             "TopPercent",           "Bottom",         "BottomPercent",
            "ContainsText",    "NotContainsText",      "BeginsWith",     "EndsWith",
            "ContainsBlanks",  "NotContainsBlanks",    "ContainsErrors", "NotContainsErrors",
            "DuplicateValues", "UniqueValues",
            "Tomorrow",        "Today",                "Yesterday",      "Last7Days",
            "NextWeek",        "ThisWeek",             "LastWeek",
            "NextMonth",       "ThisMonth",            "LastMonth",
            "AboveAverage",    "AboveOrEqualAverage",  "BelowAverage",  "BelowOrEqualAverage"
        )]
        [Alias("RuleType")]
        $ConditionalType="ContainsText"
    )

    $obj = [PSCustomObject]@{
        Text                 = $Text
        ConditionalTextColor = $ConditionalTextColor
        ConditionalType      = $ConditionalType
        PatternType          = $PatternType
        Range                = $Range
        BackgroundColor      = $BackgroundColor
    }

    $obj.pstypenames.Clear()
    $obj.pstypenames.Add("ConditionalText")
    $obj
}