Conditional formatting formula in excel 2007

davidwh000

New Member
Joined
Jul 18, 2010
Messages
21
I would like to use conditional formatting to do the following. I have a cell with a value in it and the cell to its right is marked Y or N to indicate if the value in the adjacent cell has been paid or not. I also have a running total of the amount owed in a cell elsewhere in the sheet.
How can I apply conditional formatting so that if N is in the cell it is coloured red and if Y is in the cell it is coloured green but also subtracts the value in the adjacent cell from the running total cell
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Akashwani

Well-known Member
Joined
Mar 14, 2009
Messages
2,911
Hi,

I may be wrong, but I don't think Conditional Formatting can do sums.

Use this in CF....

=$B2="Y"

and this....

=$B2="N"

These assumes that your data is in A2 & B2 down

Ak
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,064
Office Version
  1. 365
Platform
  1. Windows
Also assuming the numerical values are in column A and the Y/N values are in column B, then for the amount owing, use (adjusting ranges to ensure they are big enough):

=SUMIF(B1:B100,"N",A1:A100)
 

Watch MrExcel Video

Forum statistics

Threads
1,132,678
Messages
5,654,709
Members
418,149
Latest member
tjanok

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