signal change of cell contents and place 1 cell right

[keithkemble]

My objective is to be able to place an indicator 1 cell to the right of any cell that a user changes.

I can use the following but cannot determine what should replace the Target.Value > 100

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 1 Then
ThisRow = Target.Row
If Target.Value > 100 Then
Range("B" & ThisRow).Interior.ColorIndex = 3
Else
Range("B" & ThisRow).Interior.ColorIndex = xlColorIndexNone
End If
End If
End Sub

Any suggestions ?
Thanks

[Tom Schreiner]

If all you are looking for is a change then do away with the value condition:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 1 Then
ThisRow = Target.Row
Range("B" & ThisRow).Interior.ColorIndex = 3
Else
Range("B" & ThisRow).Interior.ColorIndex = xlColorIndexNone
End If
End Sub

Is this right?
Tom

[Bruno]

Keith, I'm not sure what you are looking for...
A) put an indicator right off any changed cell : you don't have to check the values

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

    Cells(Target.Row, Target.Column + 1).Interior.ColorIndex = 3

End Sub

B) put an indicator only if the value > 100

Private Sub Worksheet_Change(ByVal Target As Excel.Range)



If Target.Value > 100 Then

    Cells(Target.Row, Target.Column + 1).Interior.ColorIndex = 3

Else

    Cells(Target.Row, Target.Column + 1).Interior.ColorIndex = xlColorIndexNone

End If



End Sub

[keithkemble]

Thanks it works a treat.