How to highlight rows based on multiple cells

[grfbro]

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
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Job Number[/TD]
[TD]Employee[/TD]
[TD]Day Worked[/TD]
[TD]Pay Rate[/TD]
[TD]Bill Rate[/TD]
[TD]Hours[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]101[/TD]
[TD]John Doe[/TD]
[TD]Monday[/TD]
[TD]11.45[/TD]
[TD]20.00[/TD]
[TD]245[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]101[/TD]
[TD]Jack Smith[/TD]
[TD]Monday[/TD]
[TD]12.50[/TD]
[TD]21.50[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]102[/TD]
[TD]Jane Doe[/TD]
[TD]Tuesday[/TD]
[TD]10.00[/TD]
[TD]18.32[/TD]
[TD]180[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]102[/TD]
[TD]Bob Smith[/TD]
[TD]Wednesday[/TD]
[TD]14.50[/TD]
[TD]22.00[/TD]
[TD]145[/TD]
[/TR]
</tbody>[/TABLE]

WORKSHEET B
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Job Number[/TD]
[TD]Pay Rate[/TD]
[TD]Bill Rate[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]101[/TD]
[TD]11.45[/TD]
[TD]20.00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]101[/TD]
[TD]12.50[/TD]
[TD]21.50[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]102[/TD]
[TD]10.00[/TD]
[TD]18.50[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]102[/TD]
[TD]14.5[/TD]
[TD]22.00[/TD]
[/TR]
</tbody>[/TABLE]


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

 

[AhoyNC]

Try this formula in Conditional Formatting:

=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
	A	B	C	D	E	F
1	Job Number	Employee	Day Worked	Pay Rate	Bill Rate	Hours
2	101	John Doe	Monday	11.45	20	245
3	101	Jack Smith	Monday	12.5	21.5	200
4	102	Jane Doe	Tuesday	10	18.32	180
5	102	Bob Smith	Wednesday	14.5	22	145
A

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =SUMPRODUCT(--($A2=B!$A$2:$A$5),--($D2=B!$B$2:$B$5),--($E2=B!$C$2:$C$5))=0	Abc

Worksheet B

Excel Workbook
	A	B	C
1	Job Number	Pay Rate	Bill Rate
2	101	11.45	20
3	101	12.5	21.5
4	102	10	18.5
5	102	14.5	22
B

 

[grfbro]

Thanks for the help!