Announcement

Collapse
No announcement yet.

Conditional Formatting Highlighting data that matches on 2 different worksheets

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

  • #2
    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.

    Comment


    • #3
      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....
      Where there is a will there are many ways. Finding one that works for you is the challenge!

      MS Excel MVP 2010-2016

      Comment


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

        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.

        Comment


        • #5
          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.

          Comment


          • #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.

            Comment


            • #7
              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)
              Where there is a will there are many ways. Finding one that works for you is the challenge!

              MS Excel MVP 2010-2016

              Comment


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

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

                Comment

                Working...
                X