For any duplicate found the last used cell in Column a will be found, but the If statement ignores that and calls the wrong sub. Screen updating Is on.
What have I done wrong?
thanks
Steve W
Please Login or Register to view this content.
For any duplicate found the last used cell in Column a will be found, but the If statement ignores that and calls the wrong sub. Screen updating Is on.
What have I done wrong?
thanks
Steve W
Please Login or Register to view this content.
Hello swallis,
It seems to me, from your title, you are using conditional formatting to highlight the duplicates. Cell coloring applied by Conditional Formatting is not visible to VBA. Since your profile shows you are using Excel 2003, there is no easy workaround. In Excel 2007, a new Range property "DisplayFormat" was added. This will allow VBA to see Conditional Formatting colors.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Star below the post.3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
The friend who'll be using this has Excel 2010. Does that mean it could be just a matter of adding "Range.LastRow.DisplayFormat" before the if statement and it'll work? Could I be so lucky?
Hello swallis,
In your macro your are setting the cell color to yellow if there are duplicates found. After first, I thought you were checking for cells highlighted by conditional formatting. That does appear to be the case. So, If you post a copy of the workbook then I will be able to follow what you are doing. Thanks.
Thanks Leith. After your first message I made this Work around. It requires action by the user even if not a duplicate, which isn't very satisfying.
Regards,
Steve W
Last edited by AliGW; 07-14-2019 at 03:38 AM. Reason: Please don't quote unnecessarily!
I think we need the macro-enabled version (.xlsm).
EDIT: Ignore! I see now you are using Excel 2003 ...
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.
Hello swallis,
Thanks for taking the time to put this together. It makes it much easier to follow. I take it from the workbook you are philatelist.
Not me. I'm trying to help a friend record her collection.I take it from the workbook you are philatelist.
Steve W
You need to change ColorIndex to Color.
But your code is highlighting the duplicate rows, not the last row.
Thankyou, thankyou, thankyou Fluff13! That works perfectly.
The way I've cobbled it together, the last row will always be one of the duplicates.
very happy,
Steve W
You're welcome & thanks for the feedback
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks