+ Reply to Thread
Results 1 to 3 of 3

Using conditional formatting to highlight duplicate rows (VBA?)

  1. #1
    Registered User
    Join Date
    02-18-2015
    Location
    Flower Mound, TX
    MS-Off Ver
    2013
    Posts
    32

    Using conditional formatting to highlight duplicate rows (VBA?)

    Hi All,

    I'm dealing with a conditional formatting problem that has me pretty well stumped. In the attached workbook, I'm trying to apply some type of conditional formatting that highlights the identical rows that are found across each company. So for example, when you filter the data down by state (e.g. New York) it would present which rows are identical across all four companies. So New York would have "Fifth Avenue" and "Second Avenue" highlighted across all four companies, but the other rows would not be highlighted because there isn't a 1:1 match across column C and D. This conditional formatting could then be highlighted across each company's state when you filter the information down.
    In the actual data, there can be thousands of rows of data that correspond to column B when you filter it down (e.g. Company A can have 300 results, Company B will have 390, etc).

    What is the best way to go about doing this? I've been trying to come up with some VBA code, but to no avail.

    Any ideas?
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,410

    Re: Using conditional formatting to highlight duplicate rows (VBA?)

    I think this is what you want.

    I made a small pivot table from the data that contains the Company names. Then I manually filled the cells in the pivot table to match the colors on want in Column A for that company name.

    The program goes down the cells in Table1[Company:], finds the name in the pivot table, gets the color associated with that name, and applies it to the cell.

    I overlaid the pivot table with a named dynamic range so if you add or delete company names the color map adjust accordingly.
    Color_Map =OFFSET(Sheet1!$G$2,0,0,COUNTA(Sheet1!$G:$G)-1,1).
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    02-18-2015
    Location
    Flower Mound, TX
    MS-Off Ver
    2013
    Posts
    32

    Re: Using conditional formatting to highlight duplicate rows (VBA?)

    I think this will work just fine. Thanks, dflak!

+ 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. Conditional formatting to highlight rows that repeat
    By bayala87 in forum Excel General
    Replies: 1
    Last Post: 09-24-2015, 01:50 PM
  2. Conditional Formatting to highlight duplicate numbers in a range
    By mungel in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-21-2013, 02:54 PM
  3. [SOLVED] Conditional formatting of duplicate rows (not duplicate cells)
    By ExcelStefan in forum Excel General
    Replies: 2
    Last Post: 06-19-2012, 06:09 AM
  4. [SOLVED] Conditional Formatting to Highlight Entire Rows
    By christopherw34 in forum Excel General
    Replies: 4
    Last Post: 05-10-2012, 04:12 PM
  5. Replies: 1
    Last Post: 08-18-2010, 02:00 PM
  6. Conditional formatting to highlight duplicate entries
    By legendkiller420 in forum Excel General
    Replies: 1
    Last Post: 07-12-2010, 11:36 AM
  7. Conditional Formatting- Highlight Duplicate Values
    By Christopher_1 in forum Excel General
    Replies: 4
    Last Post: 04-21-2010, 11:02 AM

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