< Back
New-ConditionalText
Post
NAME New-ConditionalText
SYNOPSIS
Creates an object which describes a conditional formatting rule for single valued rules.
SYNTAX
New-ConditionalText [[-Text] <Object>] [[-ConditionalTextColor] <Object>] [[-BackgroundColor] <Object>] [[-Range]
<String>] [[-PatternType] {None | Solid | DarkGray | MediumGray | LightGray | Gray125 | Gray0625 | DarkVertical |
DarkHorizontal | DarkDown | DarkUp | DarkGrid | DarkTrellis | LightVertical | LightHorizontal | LightDown |
LightUp | LightGrid | LightTrellis}] [[-ConditionalType] <Object>] [<CommonParameters>]
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.
PARAMETERS
-Text <Object>
The text (or other value) to use in the rule. Note that Equals, GreaterThan/LessThan rules require text to
wrapped in double quotes.
Required? false
Position? 1
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-ConditionalTextColor <Object>
The font color for the cell - by default: "DarkRed".
Required? false
Position? 2
Default value [System.Drawing.Color]::DarkRed
Accept pipeline input? False
Accept wildcard characters? false
-BackgroundColor <Object>
The fill color for the cell - by default: "LightPink".
Required? false
Position? 3
Default value [System.Drawing.Color]::LightPink
Accept pipeline input? False
Accept wildcard characters? false
-Range <String>
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.
Required? false
Position? 4
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-PatternType <ExcelFillStyle>
The background pattern for the cell - by default: "Solid"
Required? false
Position? 5
Default value Solid
Accept pipeline input? False
Accept wildcard characters? false
-ConditionalType <Object>
One of the supported rules; by default "ContainsText" is selected.
Required? false
Position? 6
Default value ContainsText
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
-------------------------- EXAMPLE 1 --------------------------
PS\\> $ct = New-ConditionalText -Text 'Ferrari'
PS\\> 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 2 --------------------------
PS\\> $ct = New-ConditionalText -Text "Ferrari"
PS\\> $ct2 = New-ConditionalText -Range $worksheet.Names\\["FinishPosition"\\].Address -ConditionalType
LessThanOrEqual -Text 3 -ConditionalTextColor Red -BackgroundColor White
PS\\> 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 -Range "C:C" to specify a named column, or -Range "C2:C102" to specify certain cells in
the column.
RELATED LINKS
Add-ConditionalFormatting
New-ConditionalFormattingIconSet
SYNOPSIS
Creates an object which describes a conditional formatting rule for single valued rules.
SYNTAX
New-ConditionalText [[-Text] <Object>] [[-ConditionalTextColor] <Object>] [[-BackgroundColor] <Object>] [[-Range]
<String>] [[-PatternType] {None | Solid | DarkGray | MediumGray | LightGray | Gray125 | Gray0625 | DarkVertical |
DarkHorizontal | DarkDown | DarkUp | DarkGrid | DarkTrellis | LightVertical | LightHorizontal | LightDown |
LightUp | LightGrid | LightTrellis}] [[-ConditionalType] <Object>] [<CommonParameters>]
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.
PARAMETERS
-Text <Object>
The text (or other value) to use in the rule. Note that Equals, GreaterThan/LessThan rules require text to
wrapped in double quotes.
Required? false
Position? 1
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-ConditionalTextColor <Object>
The font color for the cell - by default: "DarkRed".
Required? false
Position? 2
Default value [System.Drawing.Color]::DarkRed
Accept pipeline input? False
Accept wildcard characters? false
-BackgroundColor <Object>
The fill color for the cell - by default: "LightPink".
Required? false
Position? 3
Default value [System.Drawing.Color]::LightPink
Accept pipeline input? False
Accept wildcard characters? false
-Range <String>
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.
Required? false
Position? 4
Default value None
Accept pipeline input? False
Accept wildcard characters? false
-PatternType <ExcelFillStyle>
The background pattern for the cell - by default: "Solid"
Required? false
Position? 5
Default value Solid
Accept pipeline input? False
Accept wildcard characters? false
-ConditionalType <Object>
One of the supported rules; by default "ContainsText" is selected.
Required? false
Position? 6
Default value ContainsText
Accept pipeline input? False
Accept wildcard characters? false
<CommonParameters>
This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (https:/go.microsoft.com/fwlink/?LinkID=113216).
INPUTS
OUTPUTS
NOTES
-------------------------- EXAMPLE 1 --------------------------
PS\\> $ct = New-ConditionalText -Text 'Ferrari'
PS\\> 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 2 --------------------------
PS\\> $ct = New-ConditionalText -Text "Ferrari"
PS\\> $ct2 = New-ConditionalText -Range $worksheet.Names\\["FinishPosition"\\].Address -ConditionalType
LessThanOrEqual -Text 3 -ConditionalTextColor Red -BackgroundColor White
PS\\> 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 -Range "C:C" to specify a named column, or -Range "C2:C102" to specify certain cells in
the column.
RELATED LINKS
Add-ConditionalFormatting
New-ConditionalFormattingIconSet