better ...."IF" statement changed

For col = 2 To 6
If col < Target(1).Column Then
Cells(row, col) = Cells(row, col) * factor
End If

"Toppers" wrote:

Change in code below. Replace all code by this new version.


Option Explicit

Dim OldValue As Variant
Dim NewValue As Variant

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
OldValue = Target(1).Formula
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim row As Long, col As Integer, factor As Double
On Error GoTo wsexit:
Application.EnableEvents = False
If Target(1).Formula < OldValue Then
'MsgBox "Used to be " & CStr(OldValue) <=== remove when tested
row = Target(1).row
factor = Target(1).Value / OldValue
For col = 2 To 6
If Cells(row, col) < Target(1) Then
Cells(row, col) = Cells(row, col) * factor
End If
Next
End If
wsexit:
Application.EnableEvents = True


End Sub

"pete321" wrote:


thanks toppers

i thought it worked, but then, now that i'm playing with it, when i
change a number, it doesnt change to what i needed it to be

first, heres what happened, i plugged it in exactly as you had it
pasted as

then it went

compile error, syntax error

i pushed ok, and

Private Sub Worksheet_Change(ByVal Target As Range)
(this line is yellow with a yellow arrow on left)

MsgBox "Used to be " & CStr(OldValue) <=== remove when tested
(this is grey)

so next, i tried to delete the grey line, so now that's gone

now if i run it, i thought it worked, but its kind of going randomly

if i change lets say fat (column E) is 10, if i try to change to 5,
instead of a 5 being in there, its 2.5. Then i hit 5 again and it goes
back to 10, the rest are all changing though simulaneously so that is
very good at least

any ideas?


--
pete321
------------------------------------------------------------------------
pete321's Profile: http://www.excelforum.com/member.php...o&userid=35354
View this thread: http://www.excelforum.com/showthread...hreadid=551285