Highlight Cell in new row when matches 2 values in a previous row

alocin

New Member
Joined
Jan 14, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Hi everyone,
I am looking for either a formula or conditional formatting for the below scenario:

- Column A = Staff member
- Column B = Date

I would like the rows to highlight if the value in both of those columns is the same across multiple rows

For example
I would like rows 2 and 4 to highlight because they have the same content in column A and B.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Select range A2 to B last row (Click A2, hold shift and press right arrow. hold ctrl + shift and press down arrow until you reach the bottom of your data)
Create a new conditional formatting (Home -> Conditional formatting -> New Rule...)
Choose 'Use a formula to determine ...'
Paste this formula in
Excel Formula:
=OR(AND($A1=$A2,$B1=$B2),AND($A3=$A2,$B3=$B2))
Choose the format you wish
 
Upvote 0
Select range A2 to B last row (Click A2, hold shift and press right arrow. hold ctrl + shift and press down arrow until you reach the bottom of your data)
Create a new conditional formatting (Home -> Conditional formatting -> New Rule...)
Choose 'Use a formula to determine ...'
Paste this formula in
Excel Formula:
=OR(AND($A1=$A2,$B1=$B2),AND($A3=$A2,$B3=$B2))
Choose the format you wish
Select range A2 to B last row (Click A2, hold shift and press right arrow. hold ctrl + shift and press down arrow until you reach the bottom of your data)
Create a new conditional formatting (Home -> Conditional formatting -> New Rule...)
Choose 'Use a formula to determine ...'
Paste this formula in
Excel Formula:
=OR(AND($A1=$A2,$B1=$B2),AND($A3=$A2,$B3=$B2))
Choose the format you wish
Unfortunately that isn’t working
 
Upvote 0
For example, in the image below, I would like row 2 and row 10 to highlight because the name and date are the same. I do not want row 6 to highlight - even though the name is the same, the date is not
 

Attachments

  • Capture.JPG
    Capture.JPG
    22.3 KB · Views: 5
Upvote 0
What is the range the conditional formatting is being applied to? the formula is made to start from "A2" only, if you select any other range than that, it probably won't work as intended.
 
Upvote 0
@alocin, welcome to the Forum. Perhaps like this?

AB
1Staff MemberDate
2A11 Jan 2021
3A15 Jan 2021
4B13 Jan 2021
5C15 Jan 2021
6D13 Jan 2021
7E12 Jan 2021
8A14 Jan 2021
9B12 Jan 2021
10C12 Jan 2021
11D12 Jan 2021
12E13 Jan 2021
13C13 Jan 2021
14D13 Jan 2021
15E14 Jan 2021
16A14 Jan 2021
17B14 Jan 2021
18C15 Jan 2021
19A13 Jan 2021
20B12 Jan 2021
21C13 Jan 2021
22D12 Jan 2021
23E16 Jan 2021
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:B23Expression=COUNTIFS($A$2:$A$23,$A2,$B$2:$B$23,$B2)>1textNO
 
Upvote 0
Solution

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top