How to highlight rows based on multiple cells

grfbro

New Member
Joined
Sep 9, 2014
Messages
2
Hello,

On my primary worksheet I have three columns that are of concern: Job Number, Pay Rate, and Bill Rate. On the second worksheet I have the same three columns that are my set numbers.

I want to highlight a row in Worksheet A if the pay rate and bill rate on worksheet A don’t match the pay rate and bill rate on worksheet B.

Most importantly, I need rows in Worksheet A to be highlighted (cell background) if the bill rate doesn't match. I want the formula to look at the Job Number and Pay Rate in Worksheet A and then find the corresponding Bill Rate in Worksheet B assigned to that Job Number/Pay Rate. If the Bill Rate in Worksheet B is different than the corresponding Bill Rate in Worksheet A, the entire row in Worksheet A would be highlighted.

WORKSHEET A
ABCDEF
1Job NumberEmployeeDay WorkedPay RateBill RateHours
2101John DoeMonday11.4520.00245
3101Jack SmithMonday12.5021.50200
4102Jane DoeTuesday10.0018.32180
5102Bob SmithWednesday14.5022.00145

<tbody>
</tbody>

WORKSHEET B
ABC
1Job NumberPay RateBill Rate
210111.4520.00
310112.5021.50
410210.0018.50
510214.522.00

<tbody>
</tbody>


Thanks for any help. I've been stuck on this for a few days.
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,719
Office Version
  1. 365
Platform
  1. Windows
Try this formula in Conditional Formatting:
Code:
=SUMPRODUCT(--($A2=B!$A$2:$A$5),--($D2=B!$B$2:$B$5),--($E2=B!$C$2:$C$5))=0

The formula returns a 1 if everything matches and a 0 if they don't.

Highlight cells in worksheet A (A2 to F5 in the example)
Go to Conditional Formatting
- New Rule
- Use Formula
- Enter above formula
- Formate
- Fill
- OK
- Ok

Excel Workbook
ABCDEF
1Job NumberEmployeeDay WorkedPay RateBill RateHours
2101John DoeMonday11.4520245
3101Jack SmithMonday12.521.5200
4102Jane DoeTuesday1018.32180
5102Bob SmithWednesday14.522145
A
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A21. / Formula is =SUMPRODUCT(--($A2=B!$A$2:$A$5),--($D2=B!$B$2:$B$5),--($E2=B!$C$2:$C$5))=0Abc



Worksheet B

Excel Workbook
ABC
1Job NumberPay RateBill Rate
210111.4520
310112.521.5
41021018.5
510214.522
B
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,322
Members
414,053
Latest member
Dual Showman

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