+ Reply to Thread
Results 1 to 6 of 6

Highlight Row based on data on separate sheet - Macro or Conditional Formatting?

  1. #1
    Registered User
    Join Date
    06-16-2011
    Location
    ohio
    MS-Off Ver
    Excel 2007
    Posts
    13

    Highlight Row based on data on separate sheet - Macro or Conditional Formatting?

    I'm trying to find a quick way to search a large list of information on two separate sheets in the same workbook for different data.

    Example:

    Sheet1:
    a1: Test1
    a2: Test2
    a3: Test3
    a4: Test4
    a5: Test5

    Sheet2:
    a1: Test1
    a2: Test2

    Basically I'd like the macro or conditional formatting (whichever is easier?) to search the data on sheet1 vs sheet2 and highlight any rows on sheet1 that aren't on sheet2. So in the example, after running the macro, Sheet1 row 3, row 4,and row 5 would all be highlighted because the values for a3, a4, and a5 are not on sheet2.

    Any help with this would be great.

    Thank you so much in advance!

    RichWW2
    Last edited by Richww2; 11-09-2012 at 06:09 PM.

  2. #2
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Highlight Row based on data on separate sheet - Macro or Conditional Formatting?

    You could use conditional formatting pretty easily...

    Step 1: Highlight column A on Sheet2 and name it searchrange (for my example formula below).
    Step 2: Highlight column A on Sheet1 and apply the following conditional formatting formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I don't remember if Excel 2007 allows for conditional formatting to use cell ranges on other sheets, but it works using a named range. Hope this helps!

    - Moo

  3. #3
    Registered User
    Join Date
    06-16-2011
    Location
    ohio
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Highlight Row based on data on separate sheet - Macro or Conditional Formatting?

    Not sure if I did it right or not, but it highlighted all the blank cells in sheet1 column A, but not Row 3, 4, or 5. I don't think the ISNA function works here because I'm looking it to highlight all the cells that don't match, even ones with values, not just the ones that are blank.
    Last edited by Richww2; 11-09-2012 at 05:57 PM.

  4. #4
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Highlight Row based on data on separate sheet - Macro or Conditional Formatting?

    OK, instead of highlighting the entire column, just highlight the cells in column A that you want compared, that way it won't highlight the entire column of blanks.

    However, it SHOULD have highlighted cells A3, A4 and A5 as well. See my attached sheet, and open up the Conditional Formatting toolbox to see the formula that is applied to those cells.

    - Moo
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-16-2011
    Location
    ohio
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Highlight Row based on data on separate sheet - Macro or Conditional Formatting?

    I did it wrong. Sorry for doubting you sir! Thank you for your help!

  6. #6
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Highlight Row based on data on separate sheet - Macro or Conditional Formatting?

    No worries! You're welcome!

    - Moo

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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