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

 

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