EXcel problem

  • Thread starter Thread starter MicrosoftBob
  • Start date Start date
M

MicrosoftBob

As an example I have a estimated monetary value in cell B4 and in cell C4 I
have the actual cost. I have set up the spreadsheet to show in cell F4:- if
the value is lower in cell C4 then the text in cell F4 is set to 'OK' if the
value is higher then the text is set to 'over budget. I wish to change the
text colour in F4 to red if the item is 'Over Budget' and blue if a returned
value is OK.

Thank you for any help
Bob
 
You have to do this in 2 stages:

First, type this formula in F4:

=IF(C4<=B4,"OK","Over Budget")

That will get you the text in F4.

Second, with F4 selected, click on Format | Conditional Formatting and
choose Formula Is rather than Cell Value Is in the first box of the
pop-up. Use this formula:

=C4<=B4

then click on the Format button, click on the Color drop-down and
choose blue. Click OK, followed by Add to set up your second
condition. This time you need Formula Is with this formula:

=C4>B4

then click Format, Color and choose red. Click OK twice to exit the
dialogue box.

That should do you. If you want the same set-up for cells below F4,
then just copy the cell down and the cell references (and Conditional
formatting) will adjust automatically.

Hope this helps.

Pete
 
Hi,

To conditionally format your cell(s):

In 2003:
1. Select the cells you want to format (column C)
2. Choose Format, Conditional Formatting
3. Choose Formula is from the first drop down
4. In the second box enter the formula:
=F4="OK"
5. Click the Format button
6. Choose a color on the Patterns tab (or any available option)
7. Click OK one.
8. Click Add
9. For Condition 2, from the first drop down pick Formula is
10. In the second box enter the formula:
=F4="Over Budget"
11. Click the Format button
1. Choose a color and click OK twice.

In 2007:
1. Highlight all the cells on the rows you want formatted
2. Choose Home, Conditional Formatting, Manage Rules, New Rule
3. Choose Use a formula to determine which cell to format
4. In the Format values where this formula is true enter the following
formula:
same formulas as above
5. Click the Format button and choose a format.
6. Click OK twice
7. New Rule
8. Choose Use a formula to determine which cell to format
9. In the Format values where this formula is true enter the following
formula:
same formula as in 2003
10. Click the Format button and choose a format.
11. Click OK tree times.--
If this helps, please click the Yes button

Cheers,
Shane Devenshire
 
Back
Top