Conditional formatting formula in excel 2007

davidwh000

New Member
Joined
Jul 18, 2010
Messages
16
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
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

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
45,918
Office Version
365
Platform
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)
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,452
Messages
5,511,468
Members
408,850
Latest member
NewUserPri

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top