Hello,
is it possible to copy a conditional format rule to another range, WITHOUT copying the base formatting of the cells?
For example, I have a rule to mark duplicate values in range A1->A20, but I need the same rule in range C1->C20, and in >600 other columns. Can I copy the rule, without touching the base formatting?
Thanks!
Welcome to the forum.
You can create a formula rule and apply it to multiple columns all at once.
There are instructions at the top of the page explaining how to attach your sample workbook.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Thanks for the (very) quick reply
But it's not exactly what I want: I want to check duplicates in each column on it's own.
I tried to attach a small example:
In the first column, NO cell should be highlighted because there are no duplicates.
In the second column, "SP" should be highlighted.
Different columns should not be compared to each other.
You might find that, if you apply Conditional Formatting to over 600 columns, the performance of your workbook could be impaired. Just a thought 💭. That would be compounded if you add and/or delete columns and/or rows.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
I know that. You are making an incorrect assumption. I'll have a look at your workbook.But it's not exactly what I want: I want to check duplicates in each column on it's own.
OK - what has possessed you to merge cells across columns??? You are making life difficult for yourself. Please get rid of those first.
https://excel.solutions/2016/10/usin...ctively-excel/
Rule for A1:
=AND(A1<>"",COUNTIF(A:A,A1)>1)
Applies to: =$A$1:$ZZ$10
Oh waw I didn't know that!
It's a legacy thing, changing this would mean tens of hours of work.
I'll think about it though.
Originally Posted by AliGW
Amazing, it works perfectly!!!
I don't quite understand the code you're using, but it works, thanks!!!
It's checking that A1 is not blank (otherwise empty cells would be highlighted) and that there is more than one of A1 in that column. Because the references are not anchored, they are dynamic.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
When you apply a Conditional Formatting formula to a range, you MUST make it relate to the top left cell in the range. In this case, Ali has applied the formula to cells =$A$1:$ZZ$10 and the formula refers to cell A1 and column A. When you make the formula references relative (as opposed to absolute), they will automatically adjust in subsequent columns and rows. You won't see it but, effectively, the formula for column B becomesand for column C it isFormula:
Please Login or Register to view this content.Formula:Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks