# How to highlight rows based on multiple cells

#### grfbro

##### New Member
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.

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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

Thanks for the help!

Replies
5
Views
374
Replies
0
Views
399
Replies
1
Views
97
Replies
0
Views
327
Replies
8
Views
470

1,212,042
Messages
6,105,570
Members
447,972
Latest member
carrieann

### 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.

### Which adblocker are you using?

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

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