Am I duplicating my rules

Printable View

09-22-2015, 08:49 AM
Larbec

Am I duplicating my rules

I have quite a bit conditional formatting and at the time I knew very little about it so I asked for some help. When I look at it it appears to be duplicating rules. Can someone tell me if these rules are duplicated or not?

Thanks

=ISODD($A11) =$H$11:$H$14

=$L$3="?"
=$BI$3:$BM$35,$C$3:$H$6,$C$11:$H$14,$C$19:$H$22,$C$29:$H$29,$C$32:$H$32,$C$ 35:$H$35,$N$3:$S$6,$N$11:$Q$14,$N$19:$Q$22,$N$29:$Q$29,$N$32:$Q$32,$N$35:$Q $35,$Y$35:$AB$35,$Y$32:$AB$32,$Y$29:$AB$29,$Y$3:$AD$6,$Y$11:$AB$14,$Y$19:$A B$22

=$L$3="?" =$G$3:$H$6,$G$11:$H$14,$G$19:$H$22,$BI$3:$BM$35,$R$3:$S$6,$AC$3:$AD$6,$G$29 :$H$29,$G$32:$H$32,$G$35:$H$35

=$A$3="?" =$G$3:$H$6,$G$11:$H$14,$G$19:$H$22,$BI$3:$BM$35,$R$3:$S$6,$AC$3:$AD$6,$G$29 :$H$29,$G$32:$H$32,$G$35:$H$35

=ISODD($A3) =$H$3:$H$6,$H$11:$H$14,$H$19:$H$22
09-22-2015, 04:29 PM
p45cal

The only ones which might be duplicating are the two =$L$3="?"
They overlap in the following cells:
$BI$3:$BM$35,$G$3:$H$6,$G$11:$H$14,$G$19:$H$22,$G$29:$H$29,$G$32:$H$32,$G$3 5:$H$35,$R$3:$S$6,$AC$3:$AD$6

Try if you can reduce it to just one:
$N$11:$Q$14,$N$19:$Q$22,$N$29:$Q$29,$N$32:$Q$32,$N$35:$Q$35,$Y$35:$AB$35,$Y $32:$AB$32,$Y$29:$AB$29,$Y$11:$AB$14,$Y$19:$AB$22,$C$3:$H$6,$C$11:$H$14,$C$ 19:$H$22,$BI$3:$BM$35,$N$3:$S$6,$Y$3:$AD$6,$C$29:$H$29,$C$32:$H$32,$C$35:$H $35

obtained from:

Code:

Sub blah() '=ISODD($A11) Set my1 = Range("$H$11:$H$14") '=$L$3="?" Set my2 = Range("$BI$3:$BM$35,$C$3:$H$6,$C$11:$H$14,$C$19:$H$22,$C$29:$H$29,$C$32:$H$32,$C$35:$H$35,$N$3:$S$6,$N$11:$Q$14,$N$19:$Q$22,$N$29:$Q$29,$N$32:$Q$32,$N$35:$Q$35,$Y$35:$AB$35,$Y$32:$AB$32,$Y$29:$AB$29,$Y$3:$AD$6,$Y$11:$AB$14,$Y$19:$AB$22") '=$L$3="?" Set my3 = Range("$G$3:$H$6,$G$11:$H$14,$G$19:$H$22,$BI$3:$BM$35,$R$3:$S$6,$AC$3:$AD$6,$G$29 :$H$29,$G$32:$H$32,$G$35:$H$35") '=$A$3="?" Set my4 = Range("$G$3:$H$6,$G$11:$H$14,$G$19:$H$22,$BI$3:$BM$35,$R$3:$S$6,$AC$3:$AD$6,$G$29 :$H$29,$G$32:$H$32,$G$35:$H$35") '=ISODD($A3) Set my5 = Range("$H$3:$H$6,$H$11:$H$14,$H$19:$H$22") my1.Select my2.Select my3.Select my4.Select my5.Select Intersect(my2, my3).Select ' overlap Debug.Print Selection.Address Set rr = Union(my2, my3) rr.Select 'consolidated Debug.Print rr.Address End Sub
09-23-2015, 02:18 AM
Larbec

Quote:

Originally Posted by p45cal

The only ones which might be duplicating are the two =$L$3="?"
They overlap in the following cells:
$BI$3:$BM$35,$G$3:$H$6,$G$11:$H$14,$G$19:$H$22,$G$29:$H$29,$G$32:$H$32,$G$3 5:$H$35,$R$3:$S$6,$AC$3:$AD$6

Try if you can reduce it to just one:
$N$11:$Q$14,$N$19:$Q$22,$N$29:$Q$29,$N$32:$Q$32,$N$35:$Q$35,$Y$35:$AB$35,$Y $32:$AB$32,$Y$29:$AB$29,$Y$11:$AB$14,$Y$19:$AB$22,$C$3:$H$6,$C$11:$H$14,$C$ 19:$H$22,$BI$3:$BM$35,$N$3:$S$6,$Y$3:$AD$6,$C$29:$H$29,$C$32:$H$32,$C$35:$H $35

obtained from:

Code:

Sub blah() '=ISODD($A11) Set my1 = Range("$H$11:$H$14") '=$L$3="?" Set my2 = Range("$BI$3:$BM$35,$C$3:$H$6,$C$11:$H$14,$C$19:$H$22,$C$29:$H$29,$C$32:$H$32,$C$35:$H$35,$N$3:$S$6,$N$11:$Q$14,$N$19:$Q$22,$N$29:$Q$29,$N$32:$Q$32,$N$35:$Q$35,$Y$35:$AB$35,$Y$32:$AB$32,$Y$29:$AB$29,$Y$3:$AD$6,$Y$11:$AB$14,$Y$19:$AB$22") '=$L$3="?" Set my3 = Range("$G$3:$H$6,$G$11:$H$14,$G$19:$H$22,$BI$3:$BM$35,$R$3:$S$6,$AC$3:$AD$6,$G$29 :$H$29,$G$32:$H$32,$G$35:$H$35") '=$A$3="?" Set my4 = Range("$G$3:$H$6,$G$11:$H$14,$G$19:$H$22,$BI$3:$BM$35,$R$3:$S$6,$AC$3:$AD$6,$G$29 :$H$29,$G$32:$H$32,$G$35:$H$35") '=ISODD($A3) Set my5 = Range("$H$3:$H$6,$H$11:$H$14,$H$19:$H$22") my1.Select my2.Select my3.Select my4.Select my5.Select Intersect(my2, my3).Select ' overlap Debug.Print Selection.Address Set rr = Union(my2, my3) rr.Select 'consolidated Debug.Print rr.Address End Sub

Thanks P45, I have several lines that are questionable. 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? Thanks again
09-23-2015, 04:25 AM
Larbec

Quote:

Originally Posted by p45cal

The only ones which might be duplicating are the two =$L$3="?"
They overlap in the following cells:
$BI$3:$BM$35,$G$3:$H$6,$G$11:$H$14,$G$19:$H$22,$G$29:$H$29,$G$32:$H$32,$G$3 5:$H$35,$R$3:$S$6,$AC$3:$AD$6

Try if you can reduce it to just one:
$N$11:$Q$14,$N$19:$Q$22,$N$29:$Q$29,$N$32:$Q$32,$N$35:$Q$35,$Y$35:$AB$35,$Y $32:$AB$32,$Y$29:$AB$29,$Y$11:$AB$14,$Y$19:$AB$22,$C$3:$H$6,$C$11:$H$14,$C$ 19:$H$22,$BI$3:$BM$35,$N$3:$S$6,$Y$3:$AD$6,$C$29:$H$29,$C$32:$H$32,$C$35:$H $35

obtained from:

Code:

Sub blah() '=ISODD($A11) Set my1 = Range("$H$11:$H$14") '=$L$3="?" Set my2 = Range("$BI$3:$BM$35,$C$3:$H$6,$C$11:$H$14,$C$19:$H$22,$C$29:$H$29,$C$32:$H$32,$C$35:$H$35,$N$3:$S$6,$N$11:$Q$14,$N$19:$Q$22,$N$29:$Q$29,$N$32:$Q$32,$N$35:$Q$35,$Y$35:$AB$35,$Y$32:$AB$32,$Y$29:$AB$29,$Y$3:$AD$6,$Y$11:$AB$14,$Y$19:$AB$22") '=$L$3="?" Set my3 = Range("$G$3:$H$6,$G$11:$H$14,$G$19:$H$22,$BI$3:$BM$35,$R$3:$S$6,$AC$3:$AD$6,$G$29 :$H$29,$G$32:$H$32,$G$35:$H$35") '=$A$3="?" Set my4 = Range("$G$3:$H$6,$G$11:$H$14,$G$19:$H$22,$BI$3:$BM$35,$R$3:$S$6,$AC$3:$AD$6,$G$29 :$H$29,$G$32:$H$32,$G$35:$H$35") '=ISODD($A3) Set my5 = Range("$H$3:$H$6,$H$11:$H$14,$H$19:$H$22") my1.Select my2.Select my3.Select my4.Select my5.Select Intersect(my2, my3).Select ' overlap Debug.Print Selection.Address Set rr = Union(my2, my3) rr.Select 'consolidated Debug.Print rr.Address End Sub

I just just noticed this was VBA code. Is this a macro I should run? Please explain. I'm a newbie but learning quickly .... I hope
09-23-2015, 05:21 AM
p45cal

Quote:

Originally Posted by Larbec

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

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.
09-23-2015, 05:25 AM
p45cal

Quote:

Originally Posted by Larbec

I just just noticed this was VBA code. Is this a macro I should run? Please explain. I'm a newbie but learning quickly .... I hope

You can run it but it won't do much; it's what I used to determine overlapping and consolidated ranges (see comments). I used your ranges and pasted them into the code.
09-23-2015, 08:28 AM
Larbec

Sorry, when I said lines I was talking about the rules that are written in the rules manager (dialogue box)

Now, I've started takeing the CF out from the dialogue box (is this what's it's called where the rules manager and CF go to)? I'm putting them in notes so I can compare them with my other sheets

i totally agree that 1 set needs to be written and the rest deleted. That's what I'm trying to do now I have 10 sheets that should be identical and they are not. Once I get 1 sheet corrected I will copy it 9 times I had someone else do the programming (writing the formulas and CF) and I've come to realize there are a lot of mistakes Not having a good understanding of all of this puts me behind the eight ball a little

ive ve been wanting to learn about recording a Macro now is just a good time and ever lol

Please keep in mind I'm a newbie. I've just gotten bold enough to look these over and start attempting to fix them. Ive been intimidated by all of this but coming around

ill look at the links you've included. Thanks