Tom's Guide | Tom's Hardware | Tom's Games

Computing.Net > Forums > Office Software > EXCEL 2007 Conditional Formatting help

Computer Problems? Computing.Net has over 1,000,000 posts about all things technology related! Over 90% answered within 24 hours! Click here to start participating now! Also, be sure to check out the New User Guide.

EXCEL 2007 Conditional Formatting help

Reply to Message Icon

Name: jcutler0902
Date: August 18, 2009 at 07:45:11 Pacific
OS: Windows XP
Product: Microsoft Microsoft excel 2007 (pc)
Subcategory: Microsoft Office
Comment:

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



Sponsored Link
Ads by Google

Response Number 1
Name: DerbyDad03
Date: August 18, 2009 at 08:48:28 Pacific
Reply:

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


0

Response Number 2
Name: jcutler0902
Date: August 18, 2009 at 09:38:45 Pacific
Reply:

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


0

Response Number 3
Name: DerbyDad03
Date: August 18, 2009 at 10:00:45 Pacific
Reply:

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.



0

Response Number 4
Name: jcutler0902
Date: August 18, 2009 at 11:01:34 Pacific
Reply:

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


0

Response Number 5
Name: DerbyDad03
Date: August 18, 2009 at 11:34:42 Pacific
Reply:

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.


0

Related Posts

See More



Response Number 6
Name: jcutler0902
Date: August 18, 2009 at 11:47:11 Pacific
Reply:

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.


0

Response Number 7
Name: DerbyDad03
Date: August 18, 2009 at 19:15:45 Pacific
Reply:

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.


0

Response Number 8
Name: jcutler0902
Date: August 19, 2009 at 07:18:02 Pacific
Reply:

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


0

Response Number 9
Name: DerbyDad03
Date: August 19, 2009 at 09:38:18 Pacific
Reply:

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.


0

Response Number 10
Name: jcutler0902
Date: August 19, 2009 at 10:36:29 Pacific
Reply:

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


0

Response Number 11
Name: jcutler0902
Date: August 19, 2009 at 12:30:43 Pacific
Reply:

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.


0

Response Number 12
Name: DerbyDad03
Date: August 19, 2009 at 16:26:08 Pacific
Reply:

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

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


0

Sponsored Link
Ads by Google
Reply to Message Icon



Post Locked

This post is quite old and has been locked from receiving new replies. Please create a new posting instead.


Go to Office Software Forum Home


Sponsored links
Ads by Google


Results for: EXCEL 2007 Conditional Formatting help
excel and conditional formatting www.computing.net/answers/office/excel-and-conditional-formatting/7374.html

Conditional Formatting in Excel 2007 www.computing.net/answers/office/conditional-formatting-in-excel-2007/9238.html

excel question - conditional format www.computing.net/answers/office/excel-question-conditional-format/7299.html