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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

aRandomHelper

Active Member
Joined
Jan 14, 2021
Messages
278
Office Version
  1. 2016
Platform
  1. Windows
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
 

alocin

New Member
Joined
Jan 14, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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
 

alocin

New Member
Joined
Jan 14, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Actually, it works. You've probably entered it incorrectly, so either explain what's wrong or share a sample workbook.
When I put this formula in, the entire column highlights
 

alocin

New Member
Joined
Jan 14, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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: 3

aRandomHelper

Active Member
Joined
Jan 14, 2021
Messages
278
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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.
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,140
Office Version
  1. 365
Platform
  1. Windows
@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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,132,644
Messages
5,654,552
Members
418,140
Latest member
ahepple86

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
Top