Excel2007
One of my sheets of reasonable size (last cell used IK1389) all of a sudden
started to display and scroll very slowly (+30s to page fwd).
I found that there are 100s (maybe 1000s - too many to count) of conditional
formats which vaguely resembled one format that I may have entered some time
ago. - Anyway, I don't want them anymore.
I suspect that they could be responsible for the sluggish behaviour.
Now I also found that when i select the whole sheet, the "Manage Rules"
window shows the majority of these rules with an empty "Applies to".

For curiosity I tried to delete one of these rules but Excel appeared to be
frozen - I cancelled it via the task manager.

Questions:
1) How can I clear all conditional formatting from a sheet without having to
go rule by rule and without interfering with (ie keeping) all other
formatting on the sheet?
2) Where could these "Applies to = blank" rules come from?

Thanks for your help,
Michael

Run this tiny macro:

Sub ClearConditionals()
Cells.FormatConditions.Delete
End Sub

Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To use the macro from the normal Excel window:

1. ALT-F8
2. Select the macro
3. Touch Run



To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

--
Gary''s Student - gsnu200908


"Michael_R" wrote:

Excel2007
One of my sheets of reasonable size (last cell used IK1389) all of a sudden
started to display and scroll very slowly (+30s to page fwd).
I found that there are 100s (maybe 1000s - too many to count) of conditional
formats which vaguely resembled one format that I may have entered some time
ago. - Anyway, I don't want them anymore.
I suspect that they could be responsible for the sluggish behaviour.
Now I also found that when i select the whole sheet, the "Manage Rules"
window shows the majority of these rules with an empty "Applies to".

For curiosity I tried to delete one of these rules but Excel appeared to be
frozen - I cancelled it via the task manager.

Questions:
1) How can I clear all conditional formatting from a sheet without having to
go rule by rule and without interfering with (ie keeping) all other
formatting on the sheet?
2) Where could these "Applies to = blank" rules come from?

Thanks for your help,
Michael

Home Tab | under styles click on conditional formatting |
Clear Rules | select the 'clear rules from entrie worksheets. |


On Oct 19, 9:13*pm, Michael_R
wrote:
Excel2007
One of my sheets of reasonable size (last cell used IK1389) all of a sudden
started to display and scroll very slowly (+30s to page fwd).
I found that there are 100s (maybe 1000s - too many to count) of conditional
formats which vaguely resembled one format that I may have entered some time
ago. - Anyway, I don't want them anymore.
I suspect that they could be responsible for the sluggish behaviour.
Now I also found that when i select the whole sheet, the "Manage Rules"
window shows the majority of these rules with an empty "Applies to".

For curiosity I tried to delete one of these rules but Excel appeared to be
frozen - I cancelled it via the task manager.

Questions:
1) How can I clear all conditional formatting from a sheet without having to
go rule by rule and without interfering with (ie keeping) all other
formatting on the sheet?
2) Where could these "Applies to = blank" rules come from?

Thanks for your help,
Michael