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

	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.00	245
3	101	Jack Smith	Monday	12.50	21.50	200
4	102	Jane Doe	Tuesday	10.00	18.32	180
5	102	Bob Smith	Wednesday	14.50	22.00	145

<tbody>
</tbody>

WORKSHEET B

	A	B	C
1	Job Number	Pay Rate	Bill Rate
2	101	11.45	20.00
3	101	12.50	21.50
4	102	10.00	18.50
5	102	14.5	22.00

<tbody>
</tbody>

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!