ContextQMD
Back to Devexpress

ConditionalFormattingCollection.AddColorScale3ConditionalFormatting(CellRange, ConditionalFormattingValue, Color, ConditionalFormattingValue, Color, ConditionalFormattingValue, Color) Method

officefileapi-devexpress-dot-spreadsheet-dot-conditionalformattingcollection-dot-i-6k-i-c.md

latest15.2 KB
Original Source

ConditionalFormattingCollection.AddColorScale3ConditionalFormatting(CellRange, ConditionalFormattingValue, Color, ConditionalFormattingValue, Color, ConditionalFormattingValue, Color) Method

Applies the three-color scale conditional formatting rule.

Namespace : DevExpress.Spreadsheet

Assembly : DevExpress.Spreadsheet.v25.2.Core.dll

NuGet Package : DevExpress.Spreadsheet.Core

Declaration

csharp
ColorScale3ConditionalFormatting AddColorScale3ConditionalFormatting(
    CellRange range,
    ConditionalFormattingValue minPoint,
    Color minPointColor,
    ConditionalFormattingValue midPoint,
    Color midPointColor,
    ConditionalFormattingValue maxPoint,
    Color maxPointColor
)
vb
Function AddColorScale3ConditionalFormatting(
    range As CellRange,
    minPoint As ConditionalFormattingValue,
    minPointColor As Color,
    midPoint As ConditionalFormattingValue,
    midPointColor As Color,
    maxPoint As ConditionalFormattingValue,
    maxPointColor As Color
) As ColorScale3ConditionalFormatting

Parameters

NameTypeDescription
rangeCellRange

A CellRange object that specifies a range of cells to which the conditional formatting rule is applied.

| | minPoint | ConditionalFormattingValue |

The ConditionalFormattingValue object that defines the minimum threshold and its ConditionalFormattingValueType type.

| | minPointColor | Color |

A Color object that defines a color representing the minimum value in the range of cells.

| | midPoint | ConditionalFormattingValue |

The ConditionalFormattingValue object that defines the midpoint threshold and its ConditionalFormattingValueType type.

| | midPointColor | Color |

A Color object that defines a color representing the midpoint value in the range of cells.

| | maxPoint | ConditionalFormattingValue |

The ConditionalFormattingValue object that defines the maximum threshold and its ConditionalFormattingValueType type.

| | maxPointColor | Color |

A Color object that defines a color representing the maximum value in the range of cells.

|

Returns

TypeDescription
ColorScale3ConditionalFormatting

A ColorScale3ConditionalFormatting object that represents the three-color scale conditional format.

|

Example

This example demonstrates how to apply a three-color scale conditional formatting rule.

  1. First of all, specify the minimum, midpoint and maximum thresholds of a range to which the rule will be applied. Threshold values are determined by the ConditionalFormattingValue object that can be accessed via the ConditionalFormattingCollection.CreateValue method. The type of the threshold value is specified by one of the ConditionalFormattingValueType enumeration values and can be a number, percent, formula, or percentile. Call the ConditionalFormattingCollection.CreateValue method with the ConditionalFormattingValueType.MinMax parameter to set the minimum and maximum thresholds to the lowest and highest values in a range of cells, respectively.
  2. To apply a conditional formatting rule represented by the ColorScale3ConditionalFormatting object, access the collection of conditional formats from the Worksheet.ConditionalFormattings property and call the ConditionalFormattingCollection.AddColorScale3ConditionalFormatting method with the following parameters:

To remove the ColorScale3ConditionalFormatting object, use the ConditionalFormattingCollection.Remove, ConditionalFormattingCollection.RemoveAt or ConditionalFormattingCollection.Clear methods.

View Example

csharp
ConditionalFormattingCollection conditionalFormattings = worksheet.ConditionalFormattings;
// Set the minimum threshold to the lowest value in the range of cells using the MIN() formula.
ConditionalFormattingValue minPoint = conditionalFormattings.CreateValue(ConditionalFormattingValueType.Formula, "=MIN($C$2:$D$15)");
// Set the midpoint threshold to the 50th percentile.
ConditionalFormattingValue midPoint = conditionalFormattings.CreateValue(ConditionalFormattingValueType.Percentile, "50");
// Set the maximum threshold to the highest value in the range of cells using the MAX() formula.
ConditionalFormattingValue maxPoint = conditionalFormattings.CreateValue(ConditionalFormattingValueType.Number, "=MAX($C$2:$D$15)");
// Create the three-color scale rule to determine how values in cells C2 through D15 vary. Red represents the lower values, yellow represents the medium values and sky blue represents the higher values.
ColorScale3ConditionalFormatting cfRule = conditionalFormattings.AddColorScale3ConditionalFormatting(worksheet.Range["$C$2:$D$15"], minPoint, Color.Red, midPoint, Color.Yellow, maxPoint, Color.SkyBlue);
vb
Dim conditionalFormattings As ConditionalFormattingCollection = worksheet.ConditionalFormattings
' Set the minimum threshold to the lowest value in the range of cells using the MIN() formula.
    Dim minPoint As ConditionalFormattingValue = conditionalFormattings.CreateValue(ConditionalFormattingValueType.Formula, "=MIN($C$2:$D$15)")
' Set the midpoint threshold to the 50th percentile.
    Dim midPoint As ConditionalFormattingValue = conditionalFormattings.CreateValue(ConditionalFormattingValueType.Percentile, "50")
' Set the maximum threshold to the highest value in the range of cells using the MAX() formula.
    Dim maxPoint As ConditionalFormattingValue = conditionalFormattings.CreateValue(ConditionalFormattingValueType.Number, "=MAX($C$2:$D$15)")
' Create the three-color scale rule to determine how values in cells C2 through D15 vary. Red represents the lower values, yellow represents the medium values and sky blue represents the higher values.
Dim cfRule As ColorScale3ConditionalFormatting = conditionalFormattings.AddColorScale3ConditionalFormatting(worksheet.Range("$C$2:$D$15"), minPoint, Color.Red, midPoint, Color.Yellow, maxPoint, Color.SkyBlue)

The following code snippets (auto-collected from DevExpress Examples) contain references to the AddColorScale3ConditionalFormatting(CellRange, ConditionalFormattingValue, Color, ConditionalFormattingValue, Color, ConditionalFormattingValue, Color) method.

Note

The algorithm used to collect these code examples remains a work in progress. Accordingly, the links and snippets below may produce inaccurate results. If you encounter an issue with code examples below, please use the feedback form on this page to report the issue.

spreadsheet-document-api-apply-conditional-formatting-to-cell-range/CS/ConditionalFormatting_Example/SpreadsheetActions/ConditionalFormatting.cs#L298

csharp
// Create the three-color scale rule to determine how values in cells C2 through D15 vary. Red represents the lower values, yellow represents the medium values and sky blue represents the higher values.
ColorScale3ConditionalFormatting cfRule = conditionalFormattings.AddColorScale3ConditionalFormatting(worksheet.Range["$C$2:$D$15"], minPoint, Color.Red, midPoint, Color.Yellow, maxPoint, Color.SkyBlue);
#endregion #ColorScale3ConditionalFormatting

winforms-spreadsheet-apply-conditional-formatting-to-a-range-of-cells/CS/SpreadsheetControl/SpreadsheetActions/ConditionalFormatting.cs#L296

csharp
// Create the three-color scale rule to determine how values in cells C2 through D15 vary. Red represents the lower values, yellow represents the medium values and sky blue represents the higher values.
ColorScale3ConditionalFormatting cfRule = conditionalFormattings.AddColorScale3ConditionalFormatting(worksheet.Range["$C$2:$D$15"], minPoint, Color.Red, midPoint, Color.Yellow, maxPoint, Color.SkyBlue);
#endregion #ColorScale3ConditionalFormatting

wpf-spreadsheet-how-to-apply-conditional-formatting-to-a-range-of-cells/CS/ConditionalFormatting_WPF_Examples/SpreadsheetActions/ConditionalFormatting.cs#L294

csharp
// Create the three-color scale rule to determine how values in cells C2 through D15 vary. Red represents the lower values, yellow represents the medium values and sky blue represents the higher values.
ColorScale3ConditionalFormatting cfRule = conditionalFormattings.AddColorScale3ConditionalFormatting(worksheet.Range["$C$2:$D$15"], minPoint, Color.Red, midPoint, Color.Yellow, maxPoint, Color.SkyBlue);
#endregion #ColorScale3ConditionalFormatting

spreadsheet-document-api-apply-conditional-formatting-to-cell-range/VB/ConditionalFormatting_Example/SpreadsheetActions/ConditionalFormatting.vb#L260

vb
' Create the three-color scale rule to determine how values in cells C2 through D15 vary. Red represents the lower values, yellow represents the medium values and sky blue represents the higher values.
                Dim cfRule As ColorScale3ConditionalFormatting = conditionalFormattings.AddColorScale3ConditionalFormatting(worksheet.Range("$C$2:$D$15"), minPoint, Color.Red, midPoint, Color.Yellow, maxPoint, Color.SkyBlue)
#End Region ' #ColorScale3ConditionalFormatting

winforms-spreadsheet-apply-conditional-formatting-to-a-range-of-cells/VB/SpreadsheetControl/SpreadsheetActions/ConditionalFormatting.vb#L257

vb
' Create the three-color scale rule to determine how values in cells C2 through D15 vary. Red represents the lower values, yellow represents the medium values and sky blue represents the higher values.
Dim cfRule As ColorScale3ConditionalFormatting = conditionalFormattings.AddColorScale3ConditionalFormatting(worksheet.Range("$C$2:$D$15"), minPoint, Color.Red, midPoint, Color.Yellow, maxPoint, Color.SkyBlue)
    ' #End Region ' #ColorScale3ConditionalFormatting

wpf-spreadsheet-how-to-apply-conditional-formatting-to-a-range-of-cells/VB/ConditionalFormatting_WPF_Examples/SpreadsheetActions/ConditionalFormatting.vb#L254

vb
' Create the three-color scale rule to determine how values in cells C2 through D15 vary. Red represents the lower values, yellow represents the medium values and sky blue represents the higher values.
                Dim cfRule As ColorScale3ConditionalFormatting = conditionalFormattings.AddColorScale3ConditionalFormatting(worksheet.Range("$C$2:$D$15"), minPoint, Color.Red, midPoint, Color.Yellow, maxPoint, Color.SkyBlue)
' #End Region ' #ColorScale3ConditionalFormatting

See Also

ConditionalFormattingCollection Interface

ConditionalFormattingCollection Members

DevExpress.Spreadsheet Namespace