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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

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
55,708
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)
 

Forum statistics

Threads
1,171,185
Messages
5,874,225
Members
433,038
Latest member
petercannas

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