Using If then to change cell colors in Excel 2007

[Randy]

I'm creating a worksheet to calculate money.. If the amount is negative I
want Excel to display the number in 'Red'.. If Positive I want to have Excel
display the number in 'Green'.. I'm sure this can be done I just can't seem
to figure out how to accomplish this

 

[David Biddulph]

Two options, either Conditional Formatting or Custom Format.

 

[pub]

I'm creating a worksheet to calculate money.. If the amount is
negative I want Excel to display the number in 'Red'.. If Positive I
want to have Excel display the number in 'Green'.. I'm sure this can
be done I just can't seem to figure out how to accomplish this

theres really 2 ways ot do this
one is conditional formatting
the other is to change the format of the cell

conditional formatting
- highlight your range
- click the big conditional formatting button
- click new rule
- choose format only cells that contain
- choose greater then
- put 0 in the box and set the font color to green
- do it again for less then zero and choose font color red

the formatting way
- right click on the cell
- choose format cells...
- choose custom
- add [Green] and [Red] so it looks something like this

[Green]0.00;[Red]0.00

i use the formatting way, its faster and takes less memory than
conditional formatting.

 

[Randy]

The 2 responses I've gotten doesn't solve my problem.. I want Excel to do
this automatically for me.. Right now I know how to highlight the range of
cells and change the font color.. but I don't want to have to do this
myself.. I want Excel to change the color as it needs to..

 

[Corey]

Try:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1") <> "" Then
If Range("A1") > 0 Then
Range("A1").Select
Selection.Font.ColorIndex = 50
Else
Range("A1").Select
Selection.Font.ColorIndex = 3
End If
End If
End Sub

Corey....

 

[David Biddulph]

Why don't you try the suggestions you've had? You've had step-by-step
instructions as to how to apply either conditional formatting or custom
formatting. [If you can't find the Conditional Formatting button to which
pub referred, use Home/ Styles/ Conditional Formatting. Similarly Custom
Formatting can be found under Home/ Cells/ Format/ Format Cells]

If you are saying that you want Excel to change its behaviour without you
giving it any commands, then sadly it won't do that, because Excel doesn't
know what you are thinking, and it needs you to tell it what to do.

If you want the conditional formatting or custom formatting to apply to a
range which covers only part of your sheet, then select that range before
you apply the CF or custom formatting. If you want to apply it to the whole
sheet, select the whole sheet either by Control-A or by clicking in the
top-left square (above row 1 and to the left of column A). In either case,
the conditional formatting or custom formatting will apply the colouring to
the cells that meet the specified conditions, and not to those that don't.
(pub's example in custom formatting would have made a zero green, but you
can adjust the format to suit your own requirements, so perhaps
[Green]General;[Red]General;General )

What else did you want?