SS Conditional Formatting

Applications

Office Productivity

Spreadsheets

SS Formatting

Colour Coding Columns

Excel

Google Sheets

Highlight Checked Checkboxes

Google Sheets

Highlight Matching Values

Google Sheets

Highlight Closest Value

Google Sheets

Highlight Cell Based on Row and Column

Protect Conditional Formatting

Clear Conditional Formatting

Colour Coding Columns

Excel

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

Colour code the columns to differentiate between Actual and Forecast
Colour code the columns to differentiate between weekdays and weekends

Conditional Formatting
New Rule...
Use a formula to determine which cells to format

=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...

SS Date Manipulation

SS RACON Functions

Google Sheets

TODO

https://infoinspired.com/google-docs/spreadsheet/date-related-conditional-formatting/

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.

SS Checkboxes

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

MySQL Memory

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()))

Protect Conditional Formatting

Cut&Paste tends to corrupt conditional formatting. To allow others to type or paste values in an Excel spreadsheet without allowing them to change formatting...

Select the range of cells where you want users to type or paste values.
Right-click → Format Cells → Protection tab.
Uncheck Locked → Click OK.
Go to Review → Protect Sheet.
In the Protect Sheet dialog:
Enter a password (optional).
Check: Select unlocked cells and Select locked cells (so they can navigate).
Uncheck: Format cells, Format columns, Format rows (this prevents any formatting changes).
Leave other options checked or unchecked as needed.
Click OK.

This is not foolproof. Educate users to use Paste Values (Ctrl-Shift-V) rather than just Paste (Ctrl-V), where possible. Or, be prepared to regularly fix the conditional formatting before it becomes so messed up that it impacts performance of the workbook.

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

https://www.excel-university.com/excel-conditional-formatting-based-on-another-cell/https://www.got-it.ai/solutions/excel-chat/excel-tutorial/conditional-formatting/conditional-formatting-orhttps://www.got-it.ai/solutions/excel-chat/excel-tutorial/and/conditional-formatting-with-andhttps://www.automateexcel.com/conditional-formatting/not-equalhttps://www.techrepublic.com/article/how-to-highlight-weekends-and-holidays-in-excel/https://infoinspired.com/google-docs/spreadsheet/date-related-conditional-formatting/https://www.howtogeek.com/439209/how-to-highlight-a-row-in-google-sheets-using-conditional-formatting/https://www.get-digital-help.com/highlight-cells-based-on-coordinates/https://support.google.com/docs/thread/70414296/how-to-highlight-a-cell-in-row-based-on-2-conditions?hl=enhttps://www.reddit.com/r/excel/comments/10hy9pd/how_do_i_make_a_cell_automatically_fill_itself/https://www.free-power-point-templates.com/articles/how-to-skip-conditional-formatting-for-blank-cells-in-excel
Highlight Closest Valuehttps://stackoverflow.com/questions/63406107/google-sheets-conditional-formatting-on-closest-value
Highlight Formulashttps://superuser.com/questions/352364/conditionally-format-a-cell-if-it-contains-formula
Clear Formattinghttps://www.extendoffice.com/documents/excel/938-excel-remove-conditional-formatting.html

Page updated

Google Sites

Report abuse