How to create and clear conditional formatting at once click via VBA?

Question

Hi,

How to use VBA to create and remove conditional formatting at once click?

My excel files is similar to the sample file - Dependent Data Validation Advanced (source: contextures), and now I am using this file as reference: https://1drv.ms/x/s!AibaAz5STOvFiCZS2sVJW80fOKx9?e=FhNL1T

I do not know how to express it more clearly, I hope the schematic diagram can help to understand, as shown below:

I use different Themed cell styles to highlighted related content; how to use the most optimized way to convert Themed cell style to Conditional formatting via VBA please?

I want to achieve this conditional formatting effect to specified tables apply to the related Table Header.

If possible, I would like to use TOGGLE to create or clear related conditional formatting with one click without affecting other conditional formatting on the same worksheet.

How can I do that please?

J.

Answer 1

Use a single cell that you toggle from TRUE to FALSE to Enable / Disable CF - in this example, cell F2 - and use the Formula option of CF, with formulas like these for the "Country" column (assuming your 'final effect' table starts in G20:I20):

=AND($F$2, LEFT(G20,1)="C") => Blue Fill

=AND($F$2, LEFT(G20,1)="U") => Red Fill

and like these for the Region column:

=AND($F$2, LEFT(H20,1)="C") => Medium Blue Fill

=AND($F$2, LEFT(H20,1)="U") => Medium Red Fill

and like these for the City column:

=AND($F$2, LEFT(I20,1)="C") => Light Blue Fill

=AND($F$2, LEFT(I20,1)="U") => Light Red Fill

Then just change the value in F2 to turn the CF on or off.

Answer 2

Hi, thanks for your help.

How can I do this by vba and /or macros please? Like this example:

https://answers.microsoft.com/en-us/msoffice/forum/all/how-to-optimize-conditional-formatting-by-vba/d344386a-b2f4-4239-ae0b-b09739851ca0.

Answer 3

If you use the formulas, the only VBA you'll need (after you put a TRUE into cell F2 - or whatever cell you are using) is this:

Sub CFMacro()

Range("F2").Value = Not Range("F2").Value

End Sub