Problem with Conditional Formatting that make my excel file hang.

[Mohd Sabri Hamzah]

Hi All,
First of all thanks even for reading this.
I have this excel file which I'm putting a lot of conditional formatting for a very good reason.
Just wondering if there's any easier way to do this or maybe an on off switch.
I've read a lot of article related to this issue, most of them giving a single sample and single solution but since my file have a lot of Conditional Formatting, it still required lot of manhour to spend to do it.
Hereby I attached a small sample of it, the original file is more than 4MB
By the way, to all human on earth, Happy New Year

 

[Hui]

Conditional Formatting (CF) can make Excel slow down if there are many of them or they are complex.
However those shown in your file definitely aren't complex

I might suggest instead of having a complex range to which the CF is applied that you simplify it and then apply it multiple times
ie: Your CF applies to the following range: =$J$14,$Q$14:$Q$15,$U$14:$U$15,$J$17:$J$29,$M$17:$M$29,$Q$17:$Q$29,$U$17:$U$29

You may be better to apply it to each range separately
eg: =$J$14
=$Q$14:$Q$15
=$U$14:$U$15
=$J$17:$J$29
=$M$17:$M$29
=$Q$17:$Q$29
=$U$17:$U$29

 

[Mohd Sabri Hamzah]

Thanks Hui, let me try first and see how it goes ...

 

[NARAYANK991]

Hi Sabri ,

There are a total of 16 CF rules ; how many do you actually need ? I would think just 2.

Narayan

 

[Hui]

I saw only 2 CF Rules ?

 

[bobhc]

As NARAYANK991 has pointed out you have 16 CFs running all duplicating the CF rules

=AND(ISBLANK(N14),M14<=$AB$6+7)

=AND(ISBLANK(N14),M14<=$AB$6)

you could just run two and speed things up read this link it will help

http://office.microsoft.com/en-gb/e...l-formatting-rule-precedence-HA010074373.aspx


.