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

 

[Legacy 98055]

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_x]

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
<pre>
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Cells(Target.Row, Target.Column + 1).Interior.ColorIndex = 3
End Sub
</pre>

B) put an indicator only if the value > 100
<pre>
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
</pre>

 

[keithkemble]

Thanks it works a treat.