Filling adjacent cells with same fill color

[watido88]

I want to make it so that if cell A1 has a negative value in it, say -10, that cells A1 and B1 both have the same fill color (red). I want it to be done automatically as soon as the value is entered into A1. How do I do this. Thanks

[BB1972]

watido,

Select Cells A1:B1, then click on Conditional Formatting (not sure exactly where it is in Excel 2007 - I think it's in the ribbon. Click New Rule, Use a formula to determine which cells to format, then type/copy:

=$A$1<0

Select your formatting, then Ok/Apply your way out, and you're done.

[watido88]

thanks BB1972, that was a lot easier than I thought it would be.

[Sean Thomas]

You can use this code that will change the cells on leaving the cell A1

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
        If Target.Value < 0 Then
         Range("A1:B1").Interior.Color = vbRed
        Else
            With Range("A1:B1").Interior
                .Pattern = xlNone
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
        End If
    End If
End Sub