Conditional Formatting on Payments with same or differenct currency

patrickteo91

Board Regular
Joined
Sep 12, 2012
Messages
88
Hi,

I need two sets of formulas to perform checking. Payment amount in column K is definitely in local currency while Column M should be in USD. My column L would be the type of currency.

First:
I need a formula to perform checking, if Currency in column L is USD, both amount in column K and M should be the same. If not the same, I want to highlight the cell from K to M in RED.

Second:
I need a formula to perform checking, if Currency in column L is not USD, both amount in column K and M must not be the same. If they are, I want to highlight the cell from K to M in BLUE.

Thanks for your help in advance.
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

miss_ell

Active Member
Joined
Jun 19, 2002
Messages
274
Hello,

Highlight the cells you want formatted, then go to Format, Conditional Formatting.

For the first condition, choose Formula Is, then paste =AND($L2="USD",$K2<>$M2), colour red.

Add a second condtion, paste =AND($L2<>"USD",$K2=$M2) in the formula box, colour blue.

I hope this helps.
 

patrickteo91

Board Regular
Joined
Sep 12, 2012
Messages
88
Many thanks miss_ell.
Its working fine.
Have a nice day & weekend ahead.



Hello,

Highlight the cells you want formatted, then go to Format, Conditional Formatting.

For the first condition, choose Formula Is, then paste =AND($L2="USD",$K2<>$M2), colour red.

Add a second condtion, paste =AND($L2<>"USD",$K2=$M2) in the formula box, colour blue.

I hope this helps.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,270
Messages
5,600,638
Members
414,398
Latest member
dhune

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