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

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

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