ConditionalFormat

public sealed class ConditionalFormat

Namespace SharpCell · package SharpCell

A conditional format: which cells of its ranges it formats, and how. Make one with a factory such as ConditionalFormat.CellValue, ConditionalFormat.Expression or ConditionalFormat.Top, change it with with (rule with { StopIfTrue = true }), and add it with Worksheet.AddConditionalFormat. SharpCell works out which cells a rule applies to (Cell.ConditionalFormats) and how a cell then looks (Cell.DisplayStyle).

Properties

Formula1

public string? Formula1 { get; }

The formula of a ConditionalFormatType.CellValue or ConditionalFormatType.Expression rule, starting with =, relative to the first cell of the rule's first range; null for other rules.

Formula2

public string? Formula2 { get; }

The second formula of a between rule; null for others.

Operator

public ConditionalOperator Operator { get; }

How a ConditionalFormatType.CellValue rule compares; ConditionalOperator.Between for other rules.

OrEqual

public bool OrEqual { get; }

Whether an above or below average rule also formats values equal to the average.

Percent

public bool Percent { get; }

Whether ConditionalFormat.Rank is a percentage.

Period

public TimePeriod Period { get; }

The period of a ConditionalFormatType.TimePeriod rule.

Rank

public int Rank { get; }

How many values (or what percentage of them) a top or bottom rule formats; 0 for others.

StandardDeviations

public int StandardDeviations { get; }

How many standard deviations above or below the average a value must be; 0 for the average itself.

StopIfTrue

public bool StopIfTrue { get; init; }

Whether lower rules are skipped for a cell this rule applies to, as Excel's Stop If True does.

Style

public ConditionalStyle? Style { get; init; }

How matching cells look; null for colour scales, data bars and icon sets, which colour each cell themselves.

Text

public string? Text { get; }

The text a text rule looks for; null for others.

Type

public ConditionalFormatType Type { get; }

What the rule looks at.

Methods

AboveAverage(ConditionalStyle, bool, int)

public static ConditionalFormat AboveAverage(ConditionalStyle style, bool orEqual = false, int standardDeviations = 0)

Numbers above the average of the ranges, or by standardDeviations standard deviations above it.

Exceptions

• ArgumentOutOfRangeException: The standard deviations are outside 0 to 3, as in Excel.

BeginsWith(string, ConditionalStyle)

public static ConditionalFormat BeginsWith(string text, ConditionalStyle style)

Cells whose text begins with text, ignoring case.

Exceptions

• ArgumentException: The text is empty.

BelowAverage(ConditionalStyle, bool, int)

public static ConditionalFormat BelowAverage(ConditionalStyle style, bool orEqual = false, int standardDeviations = 0)

Numbers below the average of the ranges, or by standardDeviations standard deviations below it.

Exceptions

• ArgumentOutOfRangeException: The standard deviations are outside 0 to 3, as in Excel.

Blanks(ConditionalStyle)

public static ConditionalFormat Blanks(ConditionalStyle style)

Empty cells and cells holding only spaces.

Bottom(int, ConditionalStyle, bool)

public static ConditionalFormat Bottom(int rank, ConditionalStyle style, bool percent = false)

The rank lowest numbers of the ranges, or the lowest rank percent of them.

Exceptions

• ArgumentOutOfRangeException: The rank is outside 1 to 1000, or a percentage outside 1 to 100.

CellValue(ConditionalOperator, string, ConditionalStyle, string)

public static ConditionalFormat CellValue(ConditionalOperator comparison, string formula1, ConditionalStyle style, string? formula2 = null)

Cells whose value compares with the formulas, such as greater than =100 or between =$B$1 and =$B$2.

Parameters

• comparison: How the value compares.
• formula1: The formula compared with, starting with =, relative to the range's first cell.
• style: How matching cells look.
• formula2: The second formula, for ConditionalOperator.Between and ConditionalOperator.NotBetween.

Exceptions

• ArgumentException: A formula is missing or empty, or a second formula is given to a comparison that takes one.

ContainsText(string, ConditionalStyle)

public static ConditionalFormat ContainsText(string text, ConditionalStyle style)

Cells whose text contains text, ignoring case.

Exceptions

• ArgumentException: The text is empty.

DateOccurring(TimePeriod, ConditionalStyle)

public static ConditionalFormat DateOccurring(TimePeriod period, ConditionalStyle style)

Dates in a period around today, as Excel's A Date Occurring.

Exceptions

• ArgumentOutOfRangeException: The period is not a defined value.

DuplicateValues(ConditionalStyle)

public static ConditionalFormat DuplicateValues(ConditionalStyle style)

Values that appear more than once in the ranges; text ignoring case.

EndsWith(string, ConditionalStyle)

public static ConditionalFormat EndsWith(string text, ConditionalStyle style)

Cells whose text ends with text, ignoring case.

Exceptions

• ArgumentException: The text is empty.

Equals(ConditionalFormat)

public bool Equals(ConditionalFormat? other)

Whether the other rule has the same settings and style; texts compared ordinally.

Errors(ConditionalStyle)

public static ConditionalFormat Errors(ConditionalStyle style)

Cells holding an error.

Expression(string, ConditionalStyle)

public static ConditionalFormat Expression(string formula, ConditionalStyle style)

Cells for which a formula is true, such as =$C2>$B2, written for the range's first cell.

Parameters

• formula: The formula, starting with =.
• style: How matching cells look.

Exceptions

• ArgumentException: The formula is empty.

GetHashCode()

public override int GetHashCode()

A hash code consistent with ConditionalFormat.Equals.

NoBlanks(ConditionalStyle)

public static ConditionalFormat NoBlanks(ConditionalStyle style)

Cells that are not blank.

NoErrors(ConditionalStyle)

public static ConditionalFormat NoErrors(ConditionalStyle style)

Cells not holding an error.

NotContainsText(string, ConditionalStyle)

public static ConditionalFormat NotContainsText(string text, ConditionalStyle style)

Cells whose text does not contain text.

Exceptions

• ArgumentException: The text is empty.

ToString()

public override string ToString()

The type, and the formula or text when the rule has one.

Top(int, ConditionalStyle, bool)

public static ConditionalFormat Top(int rank, ConditionalStyle style, bool percent = false)

The rank highest numbers of the ranges, or the highest rank percent of them.

Exceptions

• ArgumentOutOfRangeException: The rank is outside 1 to 1000, or a percentage outside 1 to 100.

UniqueValues(ConditionalStyle)

public static ConditionalFormat UniqueValues(ConditionalStyle style)

Values that appear once in the ranges.