Can conditional formatting be toggled on and off

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Can conditional formatting be toggled on and off

Post by ABabeNChrist »

I have a workbook that uses conditional formatting in various areas on various sheets. Is it possible to use code to toggle the conditional formatting on and off with the use of a checkbox.

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Can conditional formatting be toggled on and off

Post by Rudi »

Hi,

Here is a sample workbook illustrating the process.
The technique clears the formatting of a specific referenced range, and when rechecked, it assigns a fixed formatting onto the range again.
The macro is a basic concept and will not calculate a volatile range or apply multiple conditions although it is possible to modify it.
ToggleCF.xlsm
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Can conditional formatting be toggled on and off

Post by HansV »

Rudi's solution works fine, but it requires you to specify all conditional formatting rules for all ranges in VBA. If you add, remove or change conditional formatting, you will have to modify the code.

An alternative solution that doesn't require VBA is to check whether the formatting should be applied in the rules themselves. This requires all conditional formatting rules to be modified.
In Rudi's sample workbook, I set the linked cell of the check box to Sheet1!C3. So if the check box is ticked, C3 will be TRUE, and if the check box is clear, C3 will be FALSE.
The formatting rule for A2:A12 is to highlight a cell if its value is > 4.
I changed this to a formula: =AND(A2>4,$C$3)
If C3 is TRUE, this is equivalent to =A2>4, so the cell is highlighted if its value is > 4.
If C3 is FALSE, the formula will always evaluate to FALSE, so no formatting is applied, regardless of the value of A2.

See the attached version (a .xlsx since it doesn't contain code).
ToggleCF.xlsx
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans