Forum Discussion
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 have a spreadsheet and what I want to do is highlight a cell if BOTH column B AND column C (in the same row) repeat.
Can anyone help me?
6 Replies
- 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 helpsWyn,
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?)
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
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.
