Specific Conditional Formatting

zackala

New Member
Joined
Jul 5, 2021
Messages
1
Office Version
  1. 2019
Platform
  1. MacOS
Hey guys,

I am using a Spreadsheet to track my health costs. The way it works for me is I get a bill, I go out of pocket, and subsequently claim the amount from two different entities that refund me. So for example I pay a bill over $100 and then get refunded $50 from entity A and $50 from entity B. But this can take weeks and sometimes entity A or B refuses to pay, asks for more information or deducts an amount on the refund. So sometimes I get $50 from entity A, but only 40$ from entity B etc. etc.

The way that my spreadsheet is set up is that I enter the amount I payed and then enter the amounts I get refunded from entity A and B (with a few other columns for dates and other stuff). The % and copayment are calculated automatically via formulas. The make it more manageable (and because I like playing around with the spreadsheet) I would like to use conditional formatting to color in the Topic cells

I would like to have a green formatting for the Topic cell when the Copayment = $0

I would like a yellow cell for the Topic cell when the Copayment = is greater than $0 BUT not equal to the amount of the bill

I would like a red cell for the Topic Cell when the Copayment = the Bill (that would mean neither entity A or B has processed my request at all or one of them has been denied)


DateTopicBillRefund entity A in $Refund entity A in %Refund entity B in $Refund entity B in %copayment
Example A$200$10050%$10050%$0
Example B$200$9045%$10050%$10
Example C$200$200

Can anyone share the formula with me on how to do that please?

Thank you!
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,223
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Assuming that the Topic column is col B.
Select B2 downwards & use these formula.
Excel Formula:
=AND(H2=0,H2<>"")
=H2<C2
=H2=C2
 

Forum statistics

Threads
1,141,000
Messages
5,703,652
Members
421,308
Latest member
NewBlood

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