+ Reply to Thread
Results 1 to 27 of 27

Help to amend range

  1. #1
    Forum Contributor
    Join Date
    01-31-2017
    Location
    England
    MS-Off Ver
    13
    Posts
    102

    Help to amend range

    HI Experts,

    How can I amend this piece of code (can't recall where it is from - sorry) to loop through each column to highlight duplicate numbers or names within the range F to AD starting at row 10 to the last row with data and exclude these duplicate values using an array (A,0,X).

    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: Help to amend range

    This worked on your file as a normal subroutine. Place it in a normal code module.
    Please Login or Register  to view this content.

  3. #3
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Help to amend range

    Conditional formatting.
    Please Login or Register  to view this content.

  4. #4
    Forum Contributor
    Join Date
    01-31-2017
    Location
    England
    MS-Off Ver
    13
    Posts
    102

    Re: Help to amend range

    Hi Jindon,
    I think this has solved it. I will test it some more just to be sure. Thank you thank you.

  5. #5
    Forum Contributor
    Join Date
    01-31-2017
    Location
    England
    MS-Off Ver
    13
    Posts
    102

    Re: Help to amend range

    hi dangelor,

    I'm truly thankful for you assistance and help. Yours resolves it in the normal module, but doesn't allow it to have the option of highlighting as the error is occurring. I marked the other post you responded to as solved from the code you provided.
    This second topic that I posted was asking for help with highlighting as it was being entered.

  6. #6
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: Help to amend range

    Thanks for the feedback. Sorry I wasn't more helpful.

  7. #7
    Forum Contributor
    Join Date
    01-31-2017
    Location
    England
    MS-Off Ver
    13
    Posts
    102

    Re: Help to amend range

    Hi jindon,
    It works great, but the delete line in the code removes existing conditional formatting that I already use which I would like to remain. The alternative would be to incorporate them to be coloured within your code. They are the three codes excluded within the code. Also, I have tried to change the colour to be background red for duplicates rather than red font. Is that possible? Thank you:-)

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Help to amend range

    Do you need vba in the first place?

    You can set the conditional formatting manually, so that you don't need vba.

    Select F11:AD up to the row that you want to apply.
    formula
    =AND(AND(F11<>"",F11<>"A",F11<>0,F11<>"X"),COUNTIF(F:F,F11)>1)

  9. #9
    Forum Contributor
    Join Date
    01-31-2017
    Location
    England
    MS-Off Ver
    13
    Posts
    102

    Re: Help to amend range

    Yes, because I am currently using the conditional formatting and it is not stable and when user's copy a cell it then starts highlighting duplicates that are not actually duplicates. Isn't there a way to have this functionality with out deleting existing formatting rules. Your code is perfect except it removes existing formatting.

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Help to amend range

    So, are you pasting with other CF or are you setting after paste?

  11. #11
    Forum Contributor
    Join Date
    01-31-2017
    Location
    England
    MS-Off Ver
    13
    Posts
    102

    Re: Help to amend range

    Conditional formatting already exists on the page for A, 0 and X when the values are pasted and they are all coloured differently. I have a separate CF for duplicates that are not "A", "X" or 0. But as user's make changes this makes the CF unstable. Hope this makes sense.

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Help to amend range

    Not clear to me.
    If you upload a workbook showing befre/after, including existing CF, it will help to understand.

    Btw I'm not with my pc at the moment, so next reply will be aftet 2-3 hours.

  13. #13
    Forum Contributor
    Join Date
    01-31-2017
    Location
    England
    MS-Off Ver
    13
    Posts
    102

    Re: Help to amend range

    Ok, I've attached a mock up.
    Attached Files Attached Files

  14. #14
    Forum Contributor
    Join Date
    01-31-2017
    Location
    England
    MS-Off Ver
    13
    Posts
    102

    Re: Help to amend range

    There is existing conditional formatting that I don't want removed. the after sheet shows the duplicates to be in dark red

  15. #15
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Help to amend range

    Perhaps...
    Please Login or Register  to view this content.

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,421

    Re: Help to amend range

    Administrative Note:

    Please note for future reference that the thread title does not really meet our expectations.

    In future, make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles. Thanks.
    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.

  17. #17
    Forum Contributor
    Join Date
    01-31-2017
    Location
    England
    MS-Off Ver
    13
    Posts
    102

    Re: Help to amend range

    I'm sorry, I'm confused. I have the same issue and there is no difference. the existing conditional formatting is being deleted as soon as it runs.

  18. #18
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Help to amend range

    Your before sheet has no conditional format and after has.
    So I thought you are copying data from before sheet and paste only values to after sheet that has conditional formatting.

    Or do you wand all the conditional formatting via code?

  19. #19
    Forum Contributor
    Join Date
    01-31-2017
    Location
    England
    MS-Off Ver
    13
    Posts
    102

    Re: Help to amend range

    I think the easiest way is to incorporate it into the code. Is that possible?

  20. #20
    Forum Contributor
    Join Date
    01-31-2017
    Location
    England
    MS-Off Ver
    13
    Posts
    102

    Re: Help to amend range

    I've tried using a case statement to include the conditional formatting, but I can't seem to incorporate it into the code you did. I can't get it to work

  21. #21
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Help to amend range

    See if this is how you wanted.
    Please Login or Register  to view this content.

  22. #22
    Forum Contributor
    Join Date
    01-31-2017
    Location
    England
    MS-Off Ver
    13
    Posts
    102

    Re: Help to amend range

    It is so close to being perfect! Except 0 is not returning any color in the columns except when duplicated where it is then being coloured red.

    There will be many zero's in each of the columns and they should be grey background colour and should never be red as they are treated similarily to A and F in the array. The A and the F in the array, the range and the duplicates (other than 0) works perfectly.

    Thank you - so close.

  23. #23
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Help to amend range

    OK, shut dwon the pc already, so I will look at it tomorrow.

  24. #24
    Forum Contributor
    Join Date
    01-31-2017
    Location
    England
    MS-Off Ver
    13
    Posts
    102

    Re: Help to amend range

    no worries - thank you appreciate it.

  25. #25
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Help to amend range

    Try change to
    Please Login or Register  to view this content.

  26. #26
    Forum Contributor
    Join Date
    01-31-2017
    Location
    England
    MS-Off Ver
    13
    Posts
    102

    Re: Help to amend range

    I will finalise some testing, but I think this has resolved it.

  27. #27
    Forum Contributor
    Join Date
    01-31-2017
    Location
    England
    MS-Off Ver
    13
    Posts
    102

    Re: Help to amend range

    Thank you it is awesome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Amend VBA Range to exclude header row
    By robertguy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-17-2018, 08:03 AM
  2. Amend VBA Range to exclude header row
    By robertguy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-17-2018, 05:43 AM
  3. [SOLVED] VBA code to amend named range
    By pdean52 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-01-2015, 04:41 PM
  4. Amend code to do nothing if cells in a range are empty
    By nickmax1 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-05-2015, 07:38 AM
  5. [SOLVED] VBA to amend a range of Formulas
    By Simon.Ward in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-14-2013, 12:36 PM
  6. [SOLVED] How to amend the Y axis to just show the top and bottom of the range?
    By The_Snook in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 08-23-2012, 08:21 AM
  7. Amend code to change range from Col D to a Range
    By Redd in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-05-2009, 05:34 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1