Set-SLConditionalFormatIconSet


Synopsis

Apply conditional formatting Iconset on numbers.

Syntax

Description

Apply conditional formatting Iconset on numbers.
Based on the data users may select 3, 4 or 5iconsets to display data.

Parameters

-WorkBookInstance

Instance of an excel document that can be used for editing.

Required? True
Position? 0
Default value  
Accept pipeline input? true (ByValue)
Accept wildcard characters? False

-WorksheetName

This is the name of the worksheet that contains the cell range where formatting is to be applied.

Required? True
Position? 1
Default value  
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? False

-Range

The range of cells containing text to which conditional formatting has to be applied.

Required? True
Position? 2
Default value  
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? False

-IconSet

Built-in Iconset styles.
Use tab or intellisense to select from a range of possible values.
Possible values are:
&;39;FiveArrows&;39;,&;39;FiveArrowsGray&;39;,&;39;FiveQuarters&;39;,&;39;FiveRating&;39;,&;39;FourArrows&;39;,
&;39;FourArrowsGray&;39;,&;39;FourRating&;39;,&;39;FourRedToBlack&;39;,&;39;FourTrafficLights&;39;,
&;39;ThreeArrows&;39;,&;39;ThreeArrowsGray&;39;,&;39;ThreeFlags&;39;,&;39;ThreeSigns&;39;,
&;39;ThreeSymbols&;39;,&;39;ThreeSymbols2&;39;,&;39;ThreeTrafficLights1&;39;,&;39;ThreeTrafficLights2&;39;

Required? True
Position? 3
Default value  
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? False

-FiveIconSetType

Use this to apply different formatting types on 5 different ranges.

Required? True
Position? named
Default value  
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? False

-ReverseIconOrder

Reverse the order of the icons displayed.

Required? False
Position? named
Default value  
Accept pipeline input? false
Accept wildcard characters? False

-ShowIconOnly

Will show just the icons instead of icons and numbers.

Required? False
Position? named
Default value  
Accept pipeline input? false
Accept wildcard characters? False

-GreaterThanOrEqual2

True if values are to be greater than or equal to the 2nd range value.False if values are to be strictly greater than.

Required? False
Position? named
Default value  
Accept pipeline input? false
Accept wildcard characters? False

-SecondRangeValue

The 2nd Range value.

Required? False
Position? named
Default value  
Accept pipeline input? false
Accept wildcard characters? False

-SecondRangeValueType

Built-in Iconset format types.
Use tab or intellisense to select from a range of possible values.
Possible values are:
&;39;Number&;39;,&;39;Percent&;39;,&;39;Formula&;39;,&;39;Percentile&;39;

Required? True
Position? named
Default value  
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? False

-ThirdRangeValueType

Built-in Iconset format types.
Use tab or intellisense to select from a range of possible values.
Possible values are:
&;39;Number&;39;,&;39;Percent&;39;,&;39;Formula&;39;,&;39;Percentile&;39;

Required? True
Position? named
Default value  
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? False

-FourthRangeValueType

Built-in Iconset format types.
Use tab or intellisense to select from a range of possible values.
Possible values are:
&;39;Number&;39;,&;39;Percent&;39;,&;39;Formula&;39;,&;39;Percentile&;39;

Required? True
Position? named
Default value  
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? False

-FifthRangeValueType

Built-in Iconset format types.
Use tab or intellisense to select from a range of possible values.
Possible values are:
&;39;Number&;39;,&;39;Percent&;39;,&;39;Formula&;39;,&;39;Percentile&;39;

Required? True
Position? named
Default value  
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? False

-GreaterThanOrEqual3

True if values are to be greater than or equal to the 3rd range value.False if values are to be strictly greater than.

Required? False
Position? named
Default value  
Accept pipeline input? false
Accept wildcard characters? False

-ThirdRangeValue

The 3rd range value.

Required? False
Position? named
Default value  
Accept pipeline input? false
Accept wildcard characters? False

-GreaterThanOrEqual4

True if values are to be greater than or equal to the 4th range value.False if values are to be strictly greater than.

Required? False
Position? named
Default value  
Accept pipeline input? false
Accept wildcard characters? False

-FourthRangeValue

The 4th range value.
Use tab or intellisense to select from a range of possible values.
Possible values are:
&;39;Number&;39;,&;39;Percent&;39;,&;39;Formula&;39;,&;39;Percentile&;39;

Required? False
Position? named
Default value  
Accept pipeline input? false
Accept wildcard characters? False

-GreaterThanOrEqual5

True if values are to be greater than or equal to the 5th range value.False if values are to be strictly greater than.

Required? False
Position? named
Default value  
Accept pipeline input? false
Accept wildcard characters? False

-FifthRangeValue

The 5th range value.

Required? False
Position? named
Default value  
Accept pipeline input? false
Accept wildcard characters? False

-FourIconSetType

Use this to apply different formatting types on 4 different ranges.

Required? True
Position? named
Default value  
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? False

-ThreeIconSetType

Use this to apply different formatting types on 4 different ranges.

Required? True
Position? named
Default value  
Accept pipeline input? true (ByPropertyName)
Accept wildcard characters? False

This cmdlet supports the common parameters: Verbose, Debug,
ErrorAction, ErrorVariable, WarningAction, WarningVariable,
OutBuffer, PipelineVariable, and OutVariable. For more information, see
about_CommonParameters (http://go.microsoft.com/fwlink/?LinkID=113216).

Inputs

String,Int,Bool,SpreadsheetLight.SLDocument

Outputs

SpreadsheetLight.SLDocument

Notes

Examples

Example 1

Apply the conditional formatting Icon set &;39;ThreeSymbols&;39; to the range d4:d15. Both icons and values are shown

Example 2

Apply the conditional formatting Icon set &;39;FiveRating&;39; to the range f4:f15. Only icons are shown.

Example 3

Since the last example had a lot of parameters and values that scrolled off to the right this example will retain the same values\parameters
but will use a different format of data input to the cmdlet which will make it easier to read.
All the parameters and values required to run the cmdlet Set-SLConditionalFormatIconSet are stored in the variable – IconSet5Params
which is a hashtable that contains Key\Value pairs.
The keys are the parameters and the values are the parameter values.
Note: You’ll notice a little trick here. The “@” sign is followed by the variable name “IconSet5Params”, which doesn’t include the dollar sign.
The “@” sign, when used as a splat operator says,
“Take whatever characters come next and assume they’re a variable name. Assume that the variable contains a hashtable, and that the keys are parameter names.
The above explanation of the @ &;39;splat&;39; operator is a direct quote from Don jones :)

Example 4

Here we apply conditional formatting twice on two different ranges. h4:h15 & J4:J15.
The only difference between the 2 is that the second range J4;J15 has the icon order reversed.

Related links