Enable and disable conditional formatting-CodePudding

Is it possible to enable and disable, or overwrite automatic conditional formatting in Excel with a checkbox?

I figured out how to do that for custom conditional formatting, but for bars that are generated automatically, I'm not sure how to do that.

For instance something like that:

How can I make the formatting disappear when toggling the box? Specifically, that it looks like so after disabling:

Unfortunately, I cannot use vba or c# to achieve that, because sending Excel files with active macros is a security concern for a lot of people.

Is there still a way how to achieve that, eventually?

CodePudding user response：

Well, yes certainly its achievable, please follow the steps,

<blockquote  lang="en" data-id="a/Twg5A3b" data-context="false" ><a href="//imgur.com/a/Twg5A3b">Enable and disable conditional formatting</a></blockquote><script async src="//s.imgur.com/min/embed.js" charset="utf-8"></script>

• First select the range from B2:D6

• From Home Tab --> Under Styles Group --> Click Conditional Formatting,

• Click New,

• From New Formatting Rule Dialog Box --> Select Rule Type --> Format all cells based on their values,

• Under Edit the Rule Description --> Select Format Style as Data Bar

• Choose formatting accordingly and Press Ok -> Ok

• Next from Developer Tab --> Controls Menu --> Click Insert --> Click Check Box From Form Controls

• Place the Check Box in Cell F2 as shown in image,

• Now select the Check Box, right click on it, click Format Control --> Cell Link --> F2 --> Press Ok

• Next select the cell F2 --> Press CTRL 1 --> Format Cells Dialog Box opens --> Under Number Tab --> Category --> Custom --> Type --> ;;;

;;;

This hides the TRUE's & FALSE's which is occurs while checking and unchecking the Check Box,

• Select again the range B2:D6

• Press ALT H L N --> New Formatting Rule Dialog Opens --> Select the Rule Type --> Use A Formula To Determine Which Cells To Format, & enter the below formula, in the edit the rule description