Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Jasmine Grant   on Aug 26 In MS Office Category.

  
Question Answered By: Elaine Stevens   on Aug 26

I think I can simplify this.
A couple of things, though.
This doesn't make the value negative,
It REVERSES the sign of the value that is entered!
if a user enters -5, the line: .Value = .Value * -1
will change  the number  to 5!
How about this:
Target.Column gives the column  number.
since you're starting in column "F" (column 6)
subtracting 6 from the target.column will give you
the first column you're monitoring as 0 and every other
column will be an even number.
using the "mod" function, and checking for a remainder
will tell you if the number is even or odd.
so.....
Private Sub Worksheet_Change(ByVal target  as Range)
if ((target.value = "") or (target.column < 6)) then exit sub
On error  goto ws_exit:
if ((target.column - 5) mod 2 = 0) then
Application.EnableEvents = False
Range(target.address).value = abs(target.value) * -1
Application.EnableEvents = True
end if
ws_exit:
Application.EnableEvents = True
End Sub
Another thing to do is use:
If ((Not Intersect(Target, Me.Range("F3: F30")) Is Nothing) _
or (Not Intersect(Target, Me.Range("H3: H30")) Is Nothing) _
or (Not Intersect(Target, Me.Range("J3: J30")) Is Nothing) _
or (Not Intersect(Target, Me.Range("L3: L30")) Is Nothing) _
or (Not Intersect(Target, Me.Range("N3: N30")) Is Nothing) _
) Then

Share: 

 

This Question has 6 more answer(s). View Complete Question Thread

 
Didn't find what you were looking for? Find more on Change every second column to a negative number Or get search suggestion and latest updates.


Tagged: