VBA to turn off conditional formatting

[thedeadzeds]

Guys,

Is it possible to turn off a a specific part of the condition formatting? For example, range $H$2:$NH$27 Contains the following:

When the Cell value contains 'sick' then change the background yellow and font black.

So bascially i would like to have a button that i can click to turn this part of the conditional formatting on or off (but not effect any other conditional formatting applied to those cells). Initial thoughts are this can't be done but would like to check.

Thanks

 

[FormR]

Hi, you could use a cell on a hidden sheet that is used along with your rule to determine if the condition should be applied - you could then alter the value of that cell with your button.

For example, you could have a hidden sheet called "Settings" and change your conditional formatting rule to the "Use a formula" method and use this formula.

=AND(H2="Sick",Settings!$A$1=0)

Then changing the value of settings!A1 to anything other than zero/blank would negate the CF rule.