Conditional Formatting - prevent duplicate rules

[Rookie11]

HI all,

I am working on a task planning tool that requires conditional formatting to display the tasks in a certain colour. There are 20 tasks, hence 20 rules. There are 7 ranges for the 7 days of a week, all of them have the same conditional formatting layout applied. My problem is that as soon as I copy and paste my conditional formatting rules duplicate which leads to a very slow excel spreadsheet overtime.

I have already setup the conditional formatting rules so that they apply to the 7 ranges rather than setting up one rule for each range.

=$O$12:$CI$27,$O$54:$CI$69,$O$96:$CI$111,$O$138:$CI$153,$O$180:$CI$195,$O$222:$CI$237,$O$264:$CI$279

Can anyone give me advice on how I prevent duplicate rules?

[JBeaucaire]

Use COPY > PASTE SPECIAL > Values to keep the CF formulas from duplicating.

[Rookie11]

That is not always possible.
The 7 days have different opening hours which trigger a certain Data Validation allowing to slot in certain tasks during / outside of trading hours.

Because there are different stores with different trading hours I am running a macro to create the different versions which requires to copy and paste certain areas with Data Validation. This works fine for the DV however everytime I copy and paste an area with conditional formatting it creates another rule on top of the already existing rule.

I thought about clearing the conditional formatting as the first step of my macro and then applying it as the final step but was hoping someone would have another suggestion.

[Rookie11]

I solved it myself. Basically I went through the entire workbook and made sure there is no double up in any of the conditional formatting rules. Now when I copy and paste the rule doesn't duplicate any longer.
My file was soooo slow before and now runs like a charm. I had tons of duplicate rules in there which killed the file.

Thanks anyway.

[Iftikhar Arain]

Hi Rookie11, I am having same issue of creation of duplicate CF rules. Can you please explain further, what is double up in CF rules?

[AliGW]

Administrative Note:

We are happy to help, however whilst you feel your request is similar or even the same as this thread, we have a rule that you open your own thread on the issue and do not piggy back another member's thread.

Please see Forum Rule #1 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread