VBA Extending Conditional Formatting Code

Unfortunately, I can't figure out a way to accomplish what I want using the Excel GUI. I'm curious if this can be done using VBA code? The code currently marks Duplicate entries in Column D. I'd like it to extend a border around the conditional formatted cell in Column D and the cell directly to its right in Column E. Is this possible?

What The Code Does
cf1.png

What I Would Like The Code To Do
cf2.png

Please Login or Register  to view this content.

It sounds like you want to write code to set up conditional formatting rules. If you can do it with code, you can do it within the Excel GUI. (An alternative is to use code to directly modify the formatting, but that is not what your code is doing.) What is the reason you want to do this in code, rather than just setting up the CF rules?

Anyway, it is possible but it's a little tedious. Conditional formatting operates on a single cell, so you need one rule for when the row is the first of a series of duplicates, then another rule for if it's in the middle, and another rule for if it's last. And you need one set of rules for the first column, and another for the second if you do not want a line down the middle. So you need six rules. See attached for CF example. Do you need help translating this to code?

Note: This doesn't work if the numbers in D are not grouped. That is, if duplicates are not consecutive it isn't going to work.

I apologize for my late reply. I had some internet connectivity issues between paychecks.

I'm writing a massive Macro in Excel so I need the conditional formatting process automated. If I knew how to do what you've done in your example using the GUI, I would try recording that with a macro. I just don't know how to use the GUI to accomplish my goal. I'm looking at the CF rules you created and it looks like you manually created a rule for each cell (as you described would be the case for VBA). Is this the case? If so, it doesn't look like this could be automated. That sucks but at least I will know it isn't possible . Thank you so much for your help

Originally Posted by Mulsiphix

...it looks like you manually created a rule for each cell (as you described would be the case for VBA). Is this the case?

Not exactly, I created a rule for each type of cell.

You need two sets of rules. One set is for the cells in the left column, because those cells will not have a border on the right edge. The other set is for the cells in the right column, because they will not have a border on the left edge.

In each set, you need three rules: One to apply to the top cell because it has no border on the bottom, for the bottom cell because it has no border on the top, and one for the middle cells because they have no borders on the top or the bottom.

This code will create my rules:

Please Login or Register  to view this content.

The code looks awesome . Although I just noticed a problem. I see that the formulas are detecting duplicates in both Columns. Duplicates only occur in Column D in my data. So highlighting the cells next to duplicate values in column D is impossible, right ? Here is an example of my data:

example50.png

The amount of data and ultimately how many rows it will take up will be different every time I execute this macro. So I need to modify the code to detect the range. I can figure out how to settle the variable cell number per column problem, but not sure how to apply HowMany to a formula, since its a variable.

Please Login or Register  to view this content.

I read your signature and saw the line about posting pictures, so I've attached my excel file. I'm working with the data located in the sheet "CF Testing"