Is there any way I can automate the deletion of the second CF Rule

[TedX]

Hello Humans,

Due to the fact that I am a very poor programmer, in fact, it's really the design aspect that I suck at. I built the program and then did a thousand changes to it, always building onto the original and the whole thing works but there are a few niggling bit's I'd like to fix. As you can see, I have a duplicate rule and if left unattended, this would grow at the rate of one a day. That's bad news for me because I use the program 7 days a week. Currently, I'm selecting cell AC4 and manually deleting one of the rules via Conditional Formatting. Because of the way I created it all, I can't go back and redo the part, which is all copied and adjusted and positioned nightly. Is there any way I can automate (macro) deleting one of the identical rules? I don't care if it's sneaky and I use a helper you can't see I'll try any weird suggestion

 

[Micron]

Probably should try to fix what's causing it rather than repeatedly hacking it? I've never heard of this until now, so I did a bit of research. Here's one that might put you on to the cause.

From search term results for excel vba delete duplicate conditional formatting rules

Fix Excel Conditional Formatting Duplicate Rules – Contextures Blog

contexturesblog.com

 

[TedX]

Probably should try to fix what's causing it rather than repeatedly hacking it? I've never heard of this until now, so I did a bit of research. Here's one that might put you on to the cause.

Yep, that is might sound advice and may actually be the way to get where I want to go. For now, I watched the video and it was very interesting. In my case, it doesn't quite apply because I only have one cell, whereas the Lady in the tutorial had rows. Her duplicate was not an identical rule in terms of the 'Applies to' section, in my case the one cell is 100% identical. This means I can't delete one without deleting them both. I do want to keep one. It is a really nice problem (if you like problems).

Your suggestion of fixing it at the cause so it doesn't happen, rather than letting it happen and then try and fix it, is starting to loom large as the probable solution. I have an AC4 cell formatted on another worksheet, which I copy and paste onto this worksheet, after everything has been deleted. By everything deleted, clearly, I do not mean Clear Formats, the reason for that is that I require formatting for hidden rows from C to R inclusive.

It's an "I want the cake and I want to eat it too", sort of thing. I want CF, I just delete all the data at the end of the day, then I grab fresh data from a website, do some magic to it, then go and grab the formatting that's on the other worksheet I mentioned and it all comes out looking and working pretty well to suit my needs, except that one pesky AC4 cell that duplicates the rule. If that was fixed, I'd probably stop fiddling with it, it's three-plus years of work now to get to this simplified view of the program. LOL, I'm so close to finishing it. Yes, of course, something new will then come up and I'll start fiddling again, I'm only human, isn't it a rule to fiddle with it until you break it

 

[Micron]

AC4 is the only cell you have to worry about?

EDIT - and if there are 2 or more rules, they will be the same?

 

[TedX]

AC4 is the only cell you have to worry about?

Yes. It is the only cell that is formatted and duplicates the rule.

 

[Micron]

Well, if you can't figure out what causes it, then perhaps this Band-Aid will help:

Sub FixCFdupes()
Dim rng As Range
Dim i As Integer

Set rng = Sheets("7").Range("AC4")
If rng.FormatConditions.count > 1 Then
    For i = rng.FormatConditions.count To 2 Step -1
        rng.FormatConditions.Item(i).Delete
    Next
End If
Set rng = Nothing

End Sub

Change sheet name to yours.

 

[TedX]

Well, if you can't figure out what causes it, then perhaps this Band-Aid will help:

..... and there you go, we have a winner. Micron, you have done what nearly everyone said you couldn't do. As I'm reading each line of the code in an effort to actually learn something, I can see that it's really quite simple. Of course, coming up with it is hard but I love how it's just 'FormatConditions.count > 1' which leads to 'FormatConditions.count To 2 Step -1' and then delete. This is really so cool, I think that I (and others) were scared off by the mystique surrounding CF, it seems like an area that is structured to only do what it's meant to do and can't be messed with. Your code solution is totally awesome, thanks heaps. Now for kicking my butt on cleaning up the cause - I can only say this, "I know you are correct" and I'll never get any better just using 'band-aid' solutions. So, here's what I intend to do this year, I'm going to completely rebuild it from scratch, using all the lessons I learned along the way. This time however, I am going to draw the entire thing out on paper, actually design it and then build it.

For now, thanks heaps Micron, I see nothing small (pun intended) in what you did for me. I'll pay it forward when someone dumber than me joins Mr Excel

 

[Micron]

Micron, you have done what nearly everyone said you couldn't do.

I find that bewildering. I don't get it because my Excel vba is very weak indeed, while my Access vba is much more robust. It likely provides a foundation of some sort for Excel in that I tend to think of relationships between properties and methods of objects. When I don't know that relationship, that's how I base a lot of my searching. Hopefully there is nothing unstable about that solution, because of what "they" say, but it only took me about 10 minutes to derive the hierarchy of things and then maybe 10 more minutes to get something that worked for me. So I'm saying, never did it, didn't know how or even if possible but using that foundation I had to believe it was possible.

If you need an explanation of what's going on let me know, but as you say, it's pretty basic.

Speaking of Access, another thing I notice is to what enormous (to me anyway) lengths people will go in Excel to make it work like a database. Problem with converting is the learning curve I guess, but food for thought for your project.

 

[TedX]

Speaking of Access, another thing I notice is to what enormous (to me anyway) lengths people will go in Excel to make it work like a database. Problem with converting is the learning curve I guess, but food for thought for your project.

You are a very clever man Micron, you actually seem to know my issues very well. I have been toying with the idea of a database for as long as I have been working on the spreadsheet. I created a few small Access databases a decade or two ago, when Access was fairly basic, then some guru talked me into SQL which he built and I used. That was then, a fair while ago, and I drifted away from the database idea because I figured Excel is for numbers and primarily my xBet project is all about numbers. It's a rating (scoring) system for horses that compares a stack of metrics. There's no real secret here, just lots of data, and lots of numbers and the thing that makes or breaks it, is how much weight you put on certain key factors which give a higher score for one horse over another. Anyway, it's all numbers, so I instinctively thought spreadsheet, and I didn't really want to go and learn the database stuff again. Prior to this project, I never knew much about VBA but came to depend on it for doing the monotonous little steps that are done fresh every single day. Anyway, I find it remarkable that you sort of read my mind a little in the database v's spreadsheet question, I have certainly been thinking about it. Now that I have more or less finished this version of xBet, I'll give it some more thought. Thanks again and all the very best of luck to you.

 

[Micron]

You're correct about Excel & numbers I'd say. Perhaps you're already using the best tool for the job.

I once had a db that needed a chart, and since I'd rather rub sand in my eyes than use Access charts I pushed the data to named dynamic ranges in Excel and used its charting capabilities. Best of both worlds I'd say.
Glad I could help.