I am trying to compare dollar amounts in column G and I. I would like a way to have each one light up Green or Red. This is for bid information, so the lower the $$ figure would be green, red vs. versa. Thanks in advnace

Jason

Use Conditional Formatting. In 2007 I think it's under Home...Styles. How you get there is different than in 2003, but the concept is the same. See here:

http://www.homeandlearn.co.uk/excel...

Select the cells you want to format in column G, e.g. G1:G10

Then (in 2003) it's Format...Conditional Formatting...

Condition 1: Formula is... =IF(G1<I1,TRUE,FALSE) Format...Patterns...Green Condition 2: Formula is... =IF(G1>I1,TRUE,FALSE) Format...Patterns...RedSelect cells in column I.

Condition 1: Formula is... =IF(I1<G1,TRUE,FALSE) Format...Patterns...Green Condition 2: Formula is... =IF(I1>G1,TRUE,FALSE) Format...Patterns...Red

Thank you for the help.. I am still stuck unfortunately G H J

Comp 1 Price Comp 2 Price Won/Loss

$3,300.00 $5,500.00 Color needs to be Rd or Grn

$5,000.00 $4,000.00 Color needs to be Rd or GrnI have tried to do the conditional formatting on Col J2 and it is not working. I keeping getting a blank or Value..The logic formulas you have work, but an not sure how to get both of them added .

Thanks!

Jason

re: I have tried to do the conditional formatting on Col J2There is no

Col J2. There is a Column J and a cell J2, so I'll assume you mean the cell.You originally said you wanted to compare columns G & I and having the

valuebe Red or Green. Now, per your example, you have values in G & H but you said you tried to format J2.I'm confused about what you are actually trying to accomplish.

Sorry I was not clear. I am working with dollar amounts in Col G & H (Cells G2 and H2) ..and want the color to appear (Red or Green) in Col J (Cell J2) to correspond accordingly. The higher number would be Red, lower green.

Thanks again

JC

re: Sorry I was not clear.And you're still not, at least not to me.

Let's say the lower number is in G2. How is turning J2 Red or Green going to tell you anything?

G H I J 1 2 3000 4000 Green?What does a Green J2 tell you about the values in G2 and H2?

I must be missing something.

Using the below..(thanks for sticking with me here) Let's say the lower number is in G2. How is turning J2 Red or Green going to tell you anything?

G H I J

1

2 3000 4000 Green?What does a Green J2 tell you about the values in G2 and H2?

If I am comparing bid of $3000 (G2) which is MY Bid and my competitor bid $4000 (H2) J2 turning green tells me visually this was a WIN for US. Red is a loss. -

I am using the colors as a visually cue. We will use this spreadsheet at feedback in order to quote more accurately in the future.

Well, that's a bit clearer! I don't have 2007, but the concept is the same as in 2003. You need to base your formatting on the results of an IF statement. If the result is True, the pattern chosen will take effect.

I'll modify an example found at:

http://www.contextures.com/xlcondfo...

In Excel 2007:

1. Select J2

2. On the Ribbon, go to the Home tab and click Conditional Formatting

3. Click New Rule

4. Click Use a Formula to Determine Which Cells to Format

5. For the formula, enter

=(IFG2<H2,TRUE,FALSE)

6. Click the Format button.

7. Select the fill format for Green

8. Click OK, click OKNow, click New Rule but this time use:

=(IFG2>H2,TRUE,FALSE) and select the fill format for Red.

You should also think about what should happen if G2 = H2.

That worked perfect.. Thanks for working with me on this. Something else came up and wanted to ask for your help. I am trying to enter a formula to calculate the percentage of G1 and H1 to populate in K1.(This will show how much we won or the competitor won)

G H K

1 3000 4000 % of difference

2 5000 2000 % of difference

Since this question concerns a different issue, it should be posted in it's own thread with a relevant subect line. This keeps the archives a little "neater" and easier to search.

I will re-post in a new thread..Thank you again for all your help.

One other issue popped up. G H I J

1

2 3000 4000 Green?=IF(G2<H2,TRUE,FALSE)...Green

=IF(G2>H2,TRUE,FALSE)...RedWhat do I include in my formula if G OR H was left blank or $0.00. The competitor for example did not bid. If I leave it blank, my field turns red and it will not calculate correctly.

I won't have an issue with G2 = H2..but appreciate the thought on that.

=IF(AND(H2<>0,G2>H2),TRUE,FALSE)...Red =IF(OR(AND(G2<>0,H2=0),G2<H2),TRUE,FALSE)...Green

Ask Your Question

Weekly Poll

Did you watch the X-Files when it was on TV?

Discuss in The Lounge

Poll History