+ Reply to Thread
Results 1 to 14 of 14

Color each selected range and compare for unique values (without Conditional Formatting)

  1. #1
    Registered User
    Join Date
    12-12-2015
    Location
    Latvia
    MS-Off Ver
    MS Office 2016
    Posts
    13

    Question Color each selected range and compare for unique values (without Conditional Formatting)

    I have a column with more than 10000 rows.

    What I need to do is find unique value in each selection group and color in orange this group if there is unique value.

    For example numbers 2 and 3 are different and these 2 rows need to color in orange as there are unique values in the group. If there are numbers 5 and 5 (duplicate) then need to color in green both numbers in that group and so on - macro compare values and color each group separately. If there is 17 25 26 3 3 where some numbers are unique and some are duplicate then also need to color in orange this group. If there is only one number in each group then do nothing and go to the next comparison group till all routine is done.

    Sounds like very easy but I am really stuck and Google also is not on my side to help.

    Sample I have added below.

    1

    2
    3

    4

    1

    5
    5

    6

    7
    7

    8

    9
    9

    10
    10

    11

    5

    4

    9

    8

    12

    13

    14
    15

    16

    3

    17

    18
    18

    12
    19

    20
    7

    6

    12
    21
    12

    5

    18
    19
    5

    5

    22

    17
    17

    23
    23

    7
    20

    24

    17
    25
    26
    3
    3

    27
    28
    Last edited by 7absinth; 03-16-2021 at 06:21 AM.

  2. #2
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Color each selected range and compare for unique values (without Conditional Formattin

    No sample file...try this...
    Please Login or Register  to view this content.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  3. #3
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Cool Try this !


    As a VBA starter :

    PHP Code: 
    Sub Demo1()
             
    Dim Ra As Range
             Application
    .ScreenUpdating False
        
    For Each Ra In ActiveSheet.UsedRange.Columns(1).SpecialCells(xlCellTypeConstantsxlNumbers).Areas
              
    If Ra.Count 1 Then Ra.Interior.ColorIndex = Array(444_
                
    (Evaluate(Replace("--ISNUMBER(MATCH(1,COUNTIF(#,#),0))""#"Ra.Address)))
        
    Next
             Application
    .ScreenUpdating True
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon « Add Reputation » !

  4. #4
    Registered User
    Join Date
    12-12-2015
    Location
    Latvia
    MS-Off Ver
    MS Office 2016
    Posts
    13

    Re: Color each selected range and compare for unique values (without Conditional Formattin

    Thank you for your reply. Very appreciated. However macro doesn't color duplicates. I assume Frequency formulas approach is good but not for duplicates. :/

  5. #5
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow


    So try at least my post #3 demonstration as it well works on my side …
    Last edited by Marc L; 03-16-2021 at 10:43 AM. Reason: typo …

  6. #6
    Registered User
    Join Date
    12-12-2015
    Location
    Latvia
    MS-Off Ver
    MS Office 2016
    Posts
    13

    Re: Try this !

    Wow This works great. I am very impressed. Thank you. I am starter too but some parts in your script I have never seen. Room for learning tho.

  7. #7
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Color each selected range and compare for unique values (without Conditional Formattin


    Thanks to well read the direction under post #3 code and apply …

  8. #8
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow


    Thanks for the rep' added !

    Quote Originally Posted by 7absinth View Post
    but some parts in your script I have never seen. Room for learning tho.
    As all is in VBA help except for Excel basics as here the worksheet functions so to see in Excel help …

  9. #9
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Color each selected range and compare for unique values (without Conditional Formattin

    ....................................
    Last edited by sintek; 03-16-2021 at 01:25 PM.

  10. #10
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,102

    Re: Color each selected range and compare for unique values (without Conditional Formattin

    Joining the party late, but here is another solution

    Please Login or Register  to view this content.

  11. #11
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow


    Hi maniac !

    Some logic issue with your code as it fails to color the cells A91:A92 !
    One If condition for cell empty or not is useless and you can easily avoid Goto statement on a label just with easy logic …

  12. #12
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,102

    Re: Color each selected range and compare for unique values (without Conditional Formattin

    thanks for the suggestions marc l, here is the corrected code:

    Please Login or Register  to view this content.

  13. #13
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow Revamped !


    That's correct for the result but some useless still in the code (double If, Goto) …

    Your code revamped according to your Excel formula logic :

    PHP Code: 
    Sub ManiacbReloaded()
            
    Dim Rg(1) As RangeC%
            
    Set Rg(0) = Cells(Rows.Count1)
        While 
    Rg(0).Row 1
                Set Rg
    (0) = Rg(0).End(xlUp).CurrentRegion
            
    If Rg(0).Count 1 Then
                
    For Each Rg(1In Rg(0)
                    
    Application.CountIf(Rg(0), Rg(1).Value2) = 1
                    
    If C Then Exit For
                
    Next
                    Rg
    (0).Interior.ColorIndex C
            End 
    If
        
    Wend
            Erase Rg
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon « Add Reputation » !
    Last edited by Marc L; 03-17-2021 at 01:50 PM.

  14. #14
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Exclamation Re: Color each selected range and compare for unique values (without Conditional Formattin


    maniacb,

    still an issue with your last code as you can check just adding in cells A94-A97 these numbers :

    8
    9
    8
    9

    then compare the result between your code and my revamped version or my post #3 demonstration …

+ 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. Replies: 1
    Last Post: 05-22-2020, 02:46 PM
  2. VBA for conditional formatting based on user selected color
    By phrankndonna in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-29-2017, 09:55 AM
  3. Conditional formatting: how to compare multiple values
    By Aleksandramailas in forum Excel General
    Replies: 10
    Last Post: 08-24-2017, 02:15 PM
  4. Replies: 5
    Last Post: 07-27-2014, 04:43 AM
  5. [SOLVED] Sum selected values in matrix based on date range as well as unique identifier in row
    By smansyd in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-09-2014, 07:56 PM
  6. Using conditional formatting to compare values
    By Celabe in forum Excel General
    Replies: 5
    Last Post: 01-06-2010, 11:48 AM
  7. Conditional formatting: No background color when list item is selected
    By earthtodan in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-27-2008, 09:18 PM

Tags for this Thread

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