Duplicate Conditional Formatting; Stored value

[suiathon]

I have a macro that will conditional format all cells that include "cw_". If I run the macro, it will run without issue, but there will be two exactly the same conditional formats and so forth. Is there a way to check stored values against the one I want to run and if it already exists exit the code?

Please Login or Register  to view this content.

[6StringJazzer]

I have to ask why are you doing this with VBA. The rule itself is conditional upon the cell having cw_, so I wonder why you would have code that applies the rule to those cells.

It would be helpful to attach your file so we can see all of your code and get a better idea of what you are trying to do. The paper clip icon does not work for attachments. To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

[6StringJazzer]

By the way, the AND in that formula is superfluous because there is only one condition inside the AND parentheses.

[suiathon]

It is for reviewing purposes. Anyone reviewing the workpaper needs to easily see which cells include a cw_ formula which is pulling information from CaseWare software. The reason for using a conditional formatting is so that it can be applied quickly without editing information on the sheet.

Please Login or Register  to view this content.

[suiathon]

By the way, the AND in that formula is superfluous because there is only one condition inside the AND parentheses.

I probably had another criteria in there at one point and just left it as is.

[6StringJazzer]

I am not asking why you want conditional formatting, I am asking why you want to use VBA to apply it.

[suiathon]

Removing duplicate conditional formatting? So that it's a one-click solution alongside what I've already explained. If it isn't possible, then please just say so.

[6StringJazzer]

I think we are talking past each other. It is possible to achieve the formatting you want by setting up a single conditional formatting rule that applies to your cells without using VBA at all.

It's possible to use VBA to remove duplicated conditioning formatting rules, but using VBA to manage conditional formatting is complicated, and searching every possible range to identify duplicate rules is even more so. If you are determined to do that I can help but need your file with sample data and cases of duplicated CF rules to be able to reproduce exactly what you are doing.

[suiathon]

Through the Conditional Formatting tab, under styles? That's how I started and realized I didn't want to do that every single time, hence the VBA automation. For each project I can review dozens of Excel files that need this. It's also something that will probably be shared with peers at some point using a custom ribbon button. I want it to be simple for them to use as well.

I personally use VBA to automate and reduce/simplify monotonous work. I've learned primarily through recording and editing, so I don't know VBA's limitations.

If it's too complicated, I'll just continue to use the Manage Rules... option. I'm not sure, but I'd assume that the odds are in my favor that there are no other CF's in workpapers that I'll be viewing, so I can just clear all and run my code.