Forum Discussion

pioneeroutfitters's avatar
pioneeroutfitters
Copper Contributor
Dec 21, 2017

Conditional formatting

I am brand new to Excel, thrown directly into the deep end with a new job only three months ago. I have found it is incredibly cool, but the directions at times can be very mind-boggling.   I hav...
excel
Formulas and Functions
Wyn Hopkins's avatar
Wyn Hopkins
MVP
Dec 21, 2017
Ideally add an additional column and then use a formula =B1=C1 that will show True where they match or False where they don't.

Then you can highlight B1 to C100 for example and click Home > Conditional Formatting > New Rules > Use Formula

And in the formula box type =$D1 (this $ means that both the result in B and C will flag )
And apply a format

Alternatively if you can't add an extra column
Then you can highlight B1 to C100 for example and click Home > Conditional Formatting > New Rules > Use Formula

And in the formula box type =$B1=$C1
Again apply a format

Hope that helps
  • pioneeroutfitters's avatar
    pioneeroutfitters
    Copper Contributor
    Dec 22, 2017

    Wyn,

    No, this highlighted everything except what it should have. I am working on a Travel spreadsheet and I need to be alerted if the employee is traveling during the same date as already recorded. (To prevent being paid twice for paperwork error).

    So both the name (column B) and the date range (column C) - in the same row, need to repeat exactly as in another row to result in a change of color fill to alert us that something may not be right.

    (Did I explain that better?)

    • Wyn Hopkins's avatar
      Wyn Hopkins
      MVP
      Dec 22, 2017

      Ah, so in that case

       

      In D1 type  =COUNTIFS(B:B,B1,  C:C,C1)>1

       

      Copy this down

       

      This will give true whenever there is more than 1 entry with those 2 combinations

       

      You can then reference the cell D1 in the same way as mentioned earlier with the conditional formatting

       

      • pioneeroutfitters's avatar
        pioneeroutfitters
        Copper Contributor
        Dec 26, 2017

        Thank you! Awesome stuff, Excel is. (And so are those who understand it!)

  • pioneeroutfitters's avatar
    pioneeroutfitters
    Copper Contributor
    Dec 22, 2017

    Thank you! I will try this right now.

    This is for Travel and I need to be alerted if the name AND the date range repeat.

    Let's see how this works.