+ Reply to Thread
Results 1 to 8 of 8

Conditional Formatting by Group within a sheet

  1. #1
    Registered User
    Join Date
    05-16-2013
    Location
    USA
    MS-Off Ver
    Excel 2010 (Work) 2016 (Home)
    Posts
    10

    Conditional Formatting by Group within a sheet

    Hello Forum.
    I use this forum extensively for review and research yet I have never submitted a question before so hopefully someone may be able to help me figure this conundrum out .

    I spent several hours researching and trying to figure out how to conditionally format my file to highlight duplicate data within sets of data within the sheet rather than within the (whole) sheet itself. I cannot find reference to application functionality which supports this so I think it may be a custom VBA thing; to which I have little experience with.

    Although my file consists of 30,000 + records I have included a sample excel sheet of data show what it is I am trying to accomplish. My file consists of unique course numbers (col H) to corresponding descriptions (col G); a one to many relationship.... unique course# to several possible descriptions.

    I would like to highlight descriptions if they are duplicates, but only by course number groups. So the VBA would look at the range of unique records in column H (course#) and highlight column G records (course description) if column G records are duplicates. When the course number changes; repeat. Make sense?

    My intent is to be able to sort out the duplicates so I am left with only the non-duplicates for review. Otherwise I have to look at each record in my file to determine if an update is needed; yikes!

    Again, thanks for reading and helping if you can.

    Janell
    Attached Files Attached Files
    Last edited by jcraig713; 01-15-2014 at 12:31 PM.

  2. #2
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Conditional Formatting by Group within a sheet

    just run the macro
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    05-16-2013
    Location
    USA
    MS-Off Ver
    Excel 2010 (Work) 2016 (Home)
    Posts
    10

    Re: Conditional Formatting by Group within a sheet

    rcm,

    Thanks for looking at this for me. I cannot tell you what a help this is. I am not sure of the difference in sample data from what I sent previously (just cut and
    pasted) til now is but I get a debug error? I cannot include the entire original file in full as its too large have included a bigger sample original file with your macro inserted. Can you look at it?

    J




    I am getting a run time error '6': overflow message



    Sub dups()

    rmax = Range("A" & Rows.Count).End(xlUp).Row

    r = 3
    course = Cells(2, 8)
    desc = Cells(2, 7)
    While r <= rmax
    If Cells(r, 7) & Cells(r, 8) = desc & course Then
    Range("G" & r).Interior.Color = RGB(0, 0, 1000 + r * 100) ***** Error on this line *****
    End If
    course = Cells(r, 8)
    desc = Cells(r, 7)
    r = r + 1
    Wend

    End Sub

    J
    Attached Files Attached Files

  4. #4
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Conditional Formatting by Group within a sheet

    Please Login or Register  to view this content.
    I suspect that r got too big and there is no color number that big, so strike out the r*100 part

  5. #5
    Registered User
    Join Date
    05-16-2013
    Location
    USA
    MS-Off Ver
    Excel 2010 (Work) 2016 (Home)
    Posts
    10

    Re: Conditional Formatting by Group within a sheet

    You are a Rockstar! Thanks for your help. You have help me immensely.

  6. #6
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Conditional Formatting by Group within a sheet

    you are welcome mark the post as solved

  7. #7
    Registered User
    Join Date
    05-16-2013
    Location
    USA
    MS-Off Ver
    Excel 2010 (Work) 2016 (Home)
    Posts
    10

    Re: Conditional Formatting by Group within a sheet

    rcm,
    I was looking all over the reply thread area when I posted how awesome you are. I have never posted in this forum before and cannot see where to make it solved.

  8. #8
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Conditional Formatting by Group within a sheet

    it is already marked as solved

+ 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. [SOLVED] Using conditional formatting to group items
    By scottrperry in forum Excel General
    Replies: 3
    Last Post: 03-29-2019, 03:35 PM
  2. [SOLVED] Conditional Formatting - Shading by Group When Filtered
    By exs120 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-21-2013, 02:41 AM
  3. Conditional Formatting Every Other Group of Rows
    By Jsun2013 in forum Excel General
    Replies: 0
    Last Post: 04-23-2013, 04:29 PM
  4. Conditional Formatting to match a group of numbers
    By thewrathful in forum Excel General
    Replies: 2
    Last Post: 12-14-2009, 12:47 PM
  5. Conditional formatting by date-a group of cells in red
    By dzeitler in forum Excel General
    Replies: 2
    Last Post: 10-18-2006, 09:15 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