Error in Code
Of course you need a line continuation after the OR (I used your original
code and forgot to add it).
Just to be cleaner, you might limit the handler to columns farther to the
right than column 3
Private Sub Worksheet_Change(ByVal Target As Range)
if Target.Count 1 then exit sub
if Target.Column < 4 then exit sub
On Error goto ErrHandler
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("F5:F2005")) Is Nothing _
And Target.Offset(0, -3).Value = "Sale of Item" Or _
Target.Offset(0, -3).Value = "Shrinkage" Then _
if isnumeric(Target) then
Target.Value = abs(Target.Value) * -1
end if
End If
ErrHandler:
Application.EnableEvents = True
End Sub
--
Regards,
Tom Ogilvy
"Tom Ogilvy" wrote in message
...
There is nothing specifically wrong with your code. Assuming you have
this
code in the sheet module, I would do this:
Private Sub Worksheet_Change(ByVal Target As Range)
if Target.Count 1 then exit sub
On Error goto ErrHandler
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("F5:F2005")) Is Nothing _
And Target.Offset(0, -3).Value = "Sale of Item" Or
Target.Offset(0, -3).Value = "Shrinkage" Then
if isnumeric(Target) then
Target.Value = abs(Target.Value) * -1
end if
End If
ErrHandler:
Application.EnableEvents = True
End Sub
Then run code like this
Sub EventsBackOn()
Applicationl.EnableEvents = True
End Sub
then see if it works. If not, then the next step is to make sure you have
an exact match with column C values.
--
Regards,
Tom Ogilvy
"Jim May" wrote in message
news:5UHfe.2801$It1.87@lakeread02...
With:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("F5:F2005")) Is Nothing _
And Target.Offset(0, -3).Value = "Sale of Item" Or
Target.Offset(0, -3).Value = "Shrinkage" Then
Target.Value = Target.Value * -1
End If
Application.EnableEvents = True
End Sub
When I select from Col C either Sale of Item or Shrinkage and enter say
5
in
my Col F my intention (with the code above is to convert the 5 in Cell
F12
(say) to -5.
But that is not happening.
Can someone assist me?
TIA,
|