Articles

EXCEL 2007 Conditional Formatting help

August 18, 2009 at 07:45:11
Specs: Windows XP

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


See More: EXCEL 2007 Conditional Formatting help

Report •


#1
August 18, 2009 at 08:48:28

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...Red

Select 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


Report •

#2
August 18, 2009 at 09:38:45

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 Grn

I 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


Report •

#3
August 18, 2009 at 10:00:45

re: I have tried to do the conditional formatting on Col J2

There 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 value be 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.



Report •

Related Solutions

#4
August 18, 2009 at 11:01:34

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


Report •

#5
August 18, 2009 at 11:34:42

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.


Report •

#6
August 18, 2009 at 11:47:11

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.


Report •

#7
August 18, 2009 at 19:15:45

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 OK

Now, 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.


Report •

#8
August 19, 2009 at 07:18:02

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


Report •

#9
August 19, 2009 at 09:38:18

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.


Report •

#10
August 19, 2009 at 10:36:29

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

Report •

#11
August 19, 2009 at 12:30:43

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)...Red

What 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.


Report •

#12
August 19, 2009 at 16:26:08

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

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


Report •


Ask Question