Conditional Formatting Help, remove duplicates issue

[drock19]

I have done a lot of searching on here but haven't found any exact answers to my problem. I have a lot of variable data ranges in my workbook with several tabs, 2-3 times a day the workbook is wiped clean and new data is imported from SAP. In my "printing" tabs I use conditional formatting and I regularly use remove duplicates. When you use Remove Duplicates with a conditional format, it takes my selection of $N:$N and turns it into $N1:$N50, $N75, $N100, etc. So when I import data again, I have to re-do the range for every Conditional Format to catch everything.

Hopefully I was clear on my explanation, so am I using CF wrong, or can I use VBA to re-apply as I'm importing and removing duplicates. My CF Rule is simple, $N:$N, highlight anything less than 1.

[dredwolf]

If I were you, I would check the cell ranges on my conditional formatting first, it sounds like some of the ranges may have been messed up/broken, if they are supposed apply to the whole row/column, then you should only see row #/column letters, if you have breaks(ie a1:d5,a10:s27) it means the rules have ben separated,edit the ranges{Applys to} in 'Manage Rules" to be accurate..
Hope This helps

[drock19]

Yeah that's where I go to fix it every time I use Remove Duplicates, and the act removing them is creating breaks in the range. I've tried using a named range and even that becomes broken up. I basically want to "Lock" $N:$N range to conditionally format no matter what I do to it. Or use VBA to re-apply it after I've removed duplicates.

[dredwolf]

Can you upload a sample, with duplicates?
I don't see why a CF should break from a remove duplicates..
Did you use cell references or actually use ROW #'s Column letters as reference ?

[dredwolf]

What I mean is i can use 1:35 as row reference(entire row) or A:Z as column reference(entire column)

[:) Sixthsense :)]

Do Right click on the sheet tab and select view code and add the below code in the vba code window.

Whenever you apply the remove duplicates then just take your cursor to A1 cell and do double click on A1 cell which will apply the conditional formatting for Column-N.

Please Login or Register  to view this content.

[drock19]

That is awesome! Thanks!