Change all Conditional Formatting At Once?

[mhenk]

I have a sheet with a fairly large amount of conditional formatting (all the same format, different conditions).

I would like to quickly change all of the (conditional) formats, but leave the conditions as is.

Can anyone think of a quick way to do this?

 

[mhenk]

:bump:

 

[pgc01]

Hi mhenk

I read your question but I do not understand it.

Can you rephrase it, elaborate on it, post a clear working example?

Kind regards
PGC

 

[mhenk]

Simple example:

I have conditional formatting on 2 cells:

Cell A2: if Cell A1 is blank, Cell A2 is shaded blue
Cell B2: if Cell B1 is blank, Cell B2 is shaded blue

I would like to have a macro that changes all the "shaded blues" to something else...

 

[pgc01]

For your simple example, assuming the condition you are referring to is the condition no. 1:

Range("A2:B2").FormatConditions(1).Interior.ColorIndex = 3 ' Red

Hope this helps
PGC

 

[pgc01]

P. S. I assumed that you wanted the code for your simple example to tweak it and to include it in some vba program you have.

If you really just wanted to change the color you would just select the cells and use the format menu.

 

[mhenk]

:bump:

I guess what I need is a loop that will check each cell, and if it has conditional formatting, change the format, but not the conditions.

 

[Datsmart]

Select all your "Conditional Format" cells.
Open Conditional Formating dialog box and change the format as desired.
It will change just the format, not the individual conditions.

An easy way to select all Conditional Format cells:
Edit | Go to
Special...
Click Conditional formats|

 

[mhenk]

I'm getting a "You must specify criteria for all conditional formats" error. This is what I originally tried to do...

 

[Datsmart]

You are correct, if your conditional formats (CF's) are varied, you can't do them all at once.

I was able to change CF's in groups.
If you have very divergent CF's, you will have to work with just similar CF's, like all CF's in one column.