Quote Originally Posted by Larbec View Post
I have several lines that are questionable.
lines? Are we talking lines (rows) on a sheet?, lines of CF in the CF dialogue box? something else?



Quote Originally Posted by Larbec View Post
Do they make a code available that can check your CF fir errors or duplications that you know of or should I post other rules for people to help me?
'they' might: https://support.office.com/en-in/art...9-4874f7474738

This seems to be a common problem and there are several attempts to resolve it but often it's just the same CFs confined to all the rows of a single table ( http://blog.contextures.com/archives...ing-nightmare/ ), quite simple, but not in your case; it seems as you have several discrete areas of CF, but you might have to confirm this.

Some have offered solutions ( http://stackoverflow.com/questions/3...ting-excel-vba ) but I'd be checking it very carefully before I used it. Also here: http://answers.microsoft.com/en-us/o...2d12c22?auth=1

Personally, I'd either manually clean up a sheet's CF or delete all CFs and recreate them cleanly (I'd only need to do this once). Once you have a clean set of CFs on a sheet I'd record a macro doing this:

Select Cell A1
Bring up the CF dialogue box using Manage Rules…
In the Show formatting rules for: field choose This Worksheet
Select just 1 rule
Click Edit Rule…
Click OK (do no editing)
Click OK again

Stop recording the macro.
You're left with a macro that deletes all CFs on the sheet (Cells.FormatConditions.Delete), then puts them all back in again.

Then any time the CFs on the sheet get out of hand, run the macro (with the relevant sheet being the active sheet).

It's a bit hard-coded as far as macros go, and you may want to tweak it a bit to accommodate changing ranges. You could experiment; you'll see in the code lines such as Range("C4:F18").Select which you didn't do when recording the macro, and you can probably tell what CF was being applied by looking 1 or 2 lines below that. You could tweak that .Select line, or even make it dynamic, letting a few lines of code determine what to select.