SS Conditional Formatting

Colour Coding Columns

Excel

In this time recording and forecasting sheet, I want to:


=WEEKDAY(D$3)=1

=AND(OR(WEEKDAY(D$3)=1, WEEKDAY(D$3)=7),D$5="F")

=AND(OR(WEEKDAY(D$3)=1,WEEKDAY(D$3)=7),D$5="F")

=AND(OR(WEEKDAY(D$3)=1,WEEKDAY(D$3)=7),D$5="A")

This doesn't work as you might expect because the value of E3 isn't set (i.e. although D3 and E3 are merged to become one cell, only D3 contains the value we need and E3 equates to 0). We need to find a workaround...

=AND(OR(WEEKDAY(D$16)=1,WEEKDAY(D$16)=7),D$5="F")

=AND(OR(WEEKDAY(D$16)=1,WEEKDAY(D$16)=7),D$5="A")

=AND(OR(WEEKDAY(D$16)=1,WEEKDAY(D$16)=7),D$5="F")

=AND(OR(WEEKDAY(D$16)=1,WEEKDAY(D$16)=7),D$5="A")

=AND(WEEKDAY(D$16)<>1,WEEKDAY(D$16)<>7,D$5="F")

=AND(WEEKDAY(D$16)<>1,WEEKDAY(D$16)<>7,D$5="A")

For other formulas used in this spreadsheet see the linked pages below...

Google Sheets

Highlight Checked Checkboxes

Google Sheets

NOTE: Checkboxes are not usable in the same way in Excel.There is currently (2023) no Excel equivalent of this functionality.

Using the Custom formula shown to the right, you can conditionally format based on whether a checkbox is checked...

Highlight Matching Values

Google Sheets

=countif($H$1:$H$23,$H1)>1


This basically highlights duplicate values in a column.

The example sheet is from an old version of the MySQL innodb_buffer_pool_size calculator from the MySQL Memory page.

Highlight Closest Value

Google Sheets

=ABS($E$5-E9)=MIN(ARRAYFORMULA(ABS($E$5-$E$9:$E$1000)))

=ABS($F$5-F9)=MIN(ARRAYFORMULA(ABS($F$5-$F$9:$F$1000)))

=ABS($G$5-G9)=MIN(ARRAYFORMULA(ABS($G$5-$G$9:$G$1000)))

=ABS($H$5-H9)=MIN(ARRAYFORMULA(ABS($H$5-$H$9:$H$1000)))

Highlight Cell Based on Row and Column

SS Date Functions

When columns are numbered as months, and rows are numbered as years, this formula can be used to highlight the cell corresponfing to the current year and month...

=AND($A2=YEAR(TODAY()),$A2=MONTH(TODAY()))

Clear Conditional Formatting

Excel

Option 1

Home - Conditional Formatting - Clear Rules - Clear Rules from Selected Cells
Home - Conditional Formatting - Clear Rules - Clear Rules from Entire Sheet

Option 2

Developer - Visual Basic -- Insert - Module...

Sub DeleteConditionalFormats()

Dim WorkRng As Range

On Error Resume Next

xTitleId = "ClearConditional"

Set WorkRng = Application.Selection

Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)

WorkRng.FormatConditions.Delete

End Sub

F5 to run. Enter a range in prompt box

Bibliography