Conditional formatting

t1o9n9y1

New Member
Joined
Dec 9, 2018
Messages
19
JOBPERSONISSUE
OneTPKA
OneTPKA
TwoTPKB

<tbody>
</tbody>

Is it possible to change the colour of a cell if the values from both the 'PERSON' and
'ISSUE' columns match up. So because there are now two rows meeting the same criteria,
I want the cell with person in it to change to orange.

I've tried conditional formatting but it seems pretty limited, unless I'm looking at the wrong thing.

Thanks for you help.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,806
Office Version
365
Platform
Windows
In CF > New rule > Use a formula > Select the format.
=COUNTIFS($A:$A,$A2,$B:$B,$B2)>1
 

t1o9n9y1

New Member
Joined
Dec 9, 2018
Messages
19
Would it also be possible so that if there is a single duplicate, it'll turn orange. However, if a third duplicate occurs, it'll turn red? Can that be done with this formula?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,806
Office Version
365
Platform
Windows
You need to set 1 rule for each colour, so add another rule with the same formula but change the 1 to 2.
Make sure that the rule for red is above the rule for orange
 

t1o9n9y1

New Member
Joined
Dec 9, 2018
Messages
19
Okay. I can't quite get the formula to work. That table was just an example and doesn't fully reflect the actual table. So, this is what it currently looks like...
PERSON (G5-G30) ISSUE (I5-P30)

Each cell within the columns have a drop down menu. When the PERSON selection and the ISSUE selection appear together more than once, that's when I want the colour to change.
Would the formula you provided still work?

Thanks so much for your help by the way.
 

t1o9n9y1

New Member
Joined
Dec 9, 2018
Messages
19
That's more accurate I think. The PERSON column goes from cells G5-G30 and the ISSUE column goes from cells I5-P30.
 

t1o9n9y1

New Member
Joined
Dec 9, 2018
Messages
19
I can't seem to get MrExcel to work.

The table is identical to this...
... (A4-B30)... (C4-D30)... (E4-F30)Person (G4-H30)Issue (I4-P30)
TPKLate
TPKLate
NDLate
TPKAbsent
NDAbsent

<tbody>
</tbody>


So because the person and issue match in the top two rows, I want that to highlight.
I have been using the duplicate function in conditional formatting, but it seems to only work based on
one column. I want it to read both columns and when the same entries appear in another row, I want it to be highlighted.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,806
Office Version
365
Platform
Windows
That is just confusing me even more :confused:

Is "Person" a single column or 2 columns?
Is "Issue" 1 column or 8 columns?
 

Forum statistics

Threads
1,085,695
Messages
5,385,229
Members
401,936
Latest member
stephenpoff

Some videos you may like

This Week's Hot Topics

Top