Set-SLConditionalFormattingDataBars


Synopsis

Set conditional formatting data bars on a given range of cells.

Syntax

Description

Set conditional formatting data bars on a given range of cells.

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 where conditional formatting has to be applied.

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

-DataBarColor

to be used with the parameterset &;39;normal&;39;.
Use tab or intellisense to select from a range of possible values.
Possible values are:
&;39;Blue&;39;,&;39;Green&;39;,&;39;Red&;39;,&;39;Orange&;39;,&;39;LightBlue&;39;,&;39;Purple&;39;

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

-ThemeColor

to be used with the parameterset &;39;CustomDataBar1&;39;.
Use tab or intellisense to select from a range of possible values.
Possible values are:
&;39;Light1Color&;39;,&;39;Dark1Color&;39;,&;39;Light2Color&;39;,&;39;Dark2Color&;39;,&;39;Accent1Color&;39;,&;39;Accent2Color&;39;,&;39;Accent3Color&;39;,&;39;Accent4Color&;39;,&;39;Accent5Color&;39;,&;39;Accent6Color&;39;,&;39;Hyperlink&;39;,&;39;FollowedHyperlinkColor&;39;

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

-DataBarMinLength

Set the minimum length of the databar.

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

-DataBarMaxLength

Set the maximum length of the databar.

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

-DataBarType1

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

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

-DataBarType2

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

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

-MinValue

This is the minimum value from which the databar will begin.

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

-MaxValue

This is the maximum value at which the databar will end.

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

-ShowDataBarOnly

If used only the databar sans value will be shown.

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

-Color

Color of the databar.Can be used in place of themecolor.

Required? True
Position? named
Default value  
Accept pipeline input? false
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,SpreadsheetLight.SLDocument

Outputs

SpreadsheetLight.SLDocument

Notes

Examples

Example 1

apply conditional formatting on range e4:h6 with the databar color chosen as green.

Example 2

Custom databar formatting applied with accent color3 as the databar color.

Example 3

Same as the previous example but here we make 2 changes.
1 – the maximum databar length is changed from 100 to 80 and
2 – The values are hidden showing just the databars.

Example 4

At times it may be difficult to see where the bars end, because of the graduated coloring in the data bars,
so here we apply a dark fill color to the cells, and then change the font to a light color
Also we change the width of the column to 20 which makes it a little easier to see the differences in the databar lengths.
Note: since we are piping data between cmdlets we can ignore specifying the values for some of the parameters such as &;39;worksheetname&;39; and &;39;Range&;39;.
However the best practise would be to specify parameter names so that there is no cause for confusion or ambiguity.

Related links

http://www.excel-easy.com/examples/data-bars.html

Stay in touch

Linkedin // RSS Feed