Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Conditional Formatting Highlighting data that matches on 2 different worksheets

  1. #1
    Join Date
    17th July 2012
    Posts
    6

    Conditional Formatting Highlighting data that matches on 2 different worksheets

    I have 2 worksheets. One has locations with numbers, example:
    275 Location 1
    276 Location 2

    I have all my locations on the second sheet, but in 2 different columns, listed with numbers only, example:
    271 275
    272 300

    I have 2 scenarios I need help with.

    1st:
    If any of the numbers on sheet 1 match the numbers in column 1 on sheet 2, highlight the number on sheet 1 in green
    If any of the numbers on sheet 1 match the numbers in column 2 on sheet 2, highlight the number on sheet 1 in red

    2nd:
    If the number on sheet 2 matches any number on sheet 1, highlight green

    I want these to apply to all the cells that have numbers (it could apply to all cells I guess as it should ignore it if it doesn't match, I would assume)

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    17th July 2012
    Posts
    6

    Re: Conditional Formatting Highlighting data that matches on 2 different worksheets

    Sorry about the first post (I edited after I posted so it is correct now), I hit enter on the keyboard and it submitted it before I was finished typing everything.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    23rd June 2005
    Location
    Ontario, Canada
    Posts
    3,902

    Re: Conditional Formatting Highlighting data that matches on 2 different worksheets

    First name each column... so select the range in the first column of Sheet2 and name it something like List1 (you do this by typing List1 in the Name box, just to the left of the Formula bar at the top). Name the other column List2, and the column on sheet1, List3.

    Then select the List3 range, and invoke conditional formatting.

    Select to "use a formula to determine which cells to format" and then enter formula:

    =MATCH(A2,List1,0)

    where A2 is top-most cell in List3 selected.

    Click Format and choose from Fill tab (green)

    Click Ok.

    Click New Rule and repeat with formula:

    =MATCH(A2,List2,0)

    and Red

    click Ok, and ok again to finish... go to the other sheet and select both columns, and invoke conditional formatting following same rules as above, but with formula: =Match(A2,List3,0) where A2 is topleft most cell in selection....
    Microsoft MVP - Excel

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Please donate in support of my sister: Kidney Foundation of Canada -KidneyWalk 2014

  4. #4
    Join Date
    17th July 2012
    Posts
    6

    Re: Conditional Formatting Highlighting data that matches on 2 different worksheets

    Quote Originally Posted by NBVC View Post
    First name each column... so select the range in the first column of Sheet2 and name it something like List1 (you do this by typing List1 in the Name box, just to the left of the Formula bar at the top). Name the other column List2, and the column on sheet1, List3.

    Then select the List3 range, and invoke conditional formatting.

    Select to "use a formula to determine which cells to format" and then enter formula:

    =MATCH(A2,List1,0)

    where A2 is top-most cell in List3 selected.

    Click Format and choose from Fill tab (green)

    Click Ok.

    Click New Rule and repeat with formula:

    =MATCH(A2,List2,0)

    and Red

    click Ok, and ok again to finish... go to the other sheet and select both columns, and invoke conditional formatting following same rules as above, but with formula: =Match(A2,List3,0) where A2 is topleft most cell in selection....
    Thanks!!!! The first 2 work fine, but the last one does nothing. Not sure what I am doing wrong.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    17th July 2012
    Posts
    6

    Re: Conditional Formatting Highlighting data that matches on 2 different worksheets

    Actually it looks like it applied the first rule to not everything. Not sure why. For instance, in column B on sheet 2 there is number 325 and on sheet1 there is a 325 and nothing is applied to it. I may just have to go through this manually.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    17th July 2012
    Posts
    6

    Re: Conditional Formatting Highlighting data that matches on 2 different worksheets

    I deleted all the rules and redid them, I also formatted all the cells the same before I applied the rules and the first 2 are doing what they are suppose to, but I cannot get rule 3 to do anything.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    23rd June 2005
    Location
    Ontario, Canada
    Posts
    3,902

    Re: Conditional Formatting Highlighting data that matches on 2 different worksheets

    Sorry that is my mistake... MATCH() only works on one row or column

    Try:

    =COUNTIF(List3,A2)
    Microsoft MVP - Excel

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Please donate in support of my sister: Kidney Foundation of Canada -KidneyWalk 2014

  8. #8
    Join Date
    17th July 2012
    Posts
    6

    Re: Conditional Formatting Highlighting data that matches on 2 different worksheets

    That got it!!! You da man NBVC!!!

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 8
    Last Post: May 27th, 2011, 03:11
  2. Replies: 20
    Last Post: July 18th, 2006, 10:59
  3. Replies: 4
    Last Post: April 7th, 2005, 00:54
  4. Replies: 1
    Last Post: December 2nd, 2003, 04:03
  5. Replies: 4
    Last Post: July 3rd, 2003, 02:51

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
  •  
porno