Change all Conditional Formatting At Once?

mhenk

Well-known Member
Joined
Jun 13, 2005
Messages
591
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?
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,887
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

Well-known Member
Joined
Jun 13, 2005
Messages
591
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,887
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,887
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

Well-known Member
Joined
Jun 13, 2005
Messages
591
: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

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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

Well-known Member
Joined
Jun 13, 2005
Messages
591
I'm getting a "You must specify criteria for all conditional formats" error. This is what I originally tried to do...
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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.
 

Forum statistics

Threads
1,181,102
Messages
5,928,062
Members
436,586
Latest member
latintxn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top