range only if A3=1

[mikespeck]

Hi hopefully someone can help me with this. The following is some VBA code that was written however when anything changes on row 3 it automatically drops down. I don't want anything to drop down unless A3 = 1, can anyone help me?Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range(Target.Address), Me.Range("A3:H3")) _
Is Nothing Then
Me.Cells(Me.Range("A:A").Rows.Count, Target.Column).Clear
Dim rgOldValues As Range
Dim iLastRow As Long
iLastRow = Me.Cells(Columns(Target.Column).Rows.Count, Target.Column) _
.End(xlUp).row
Application.EnableEvents = False
Select Case iLastRow
Case 1
Case 2
Case 3
Range("A4:H4").Value = Range("A3:H3").Value
Range("C4").Value = Now
Cells(4, Target.Column).Value = Cells(3, Target.Column).Value
Case Else
vaOldValues = Me.Range("A4:H" & _
IIf(iLastRow = 4, 5, iLastRow))
Range("A5:H5").Resize(UBound(vaOldValues, 1), 6).Value = _
vaOldValues
Range("A4:H4").Value = Range("A3:H3").Value
Range("C4").Value = Now
Set rgOldValues = Me.Range(Cells(Target.row + 2, Target.Column), _
Cells(iLastRow, Target.Column))
Cells(4, Target.Column).Value = Cells(3, Target.Column).Value
End Select
Application.EnableEvents = True
End If

End Sub

[Gary''s Student]

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A3").Value <>1 Then
Exit Sub
End If
..
..
..


will cause the macro to do nothing unless A3=1
--
Gary's Student


"mikespeck" wrote:

>
> Hi hopefully someone can help me with this. The following is some VBA
> code that was written however when anything changes on row 3 it
> automatically drops down. I don't want anything to drop down unless
> A3 = 1, can anyone help me?Private Sub Worksheet_Change(ByVal Target As
> Range)
> If Not Intersect(Range(Target.Address), Me.Range("A3:H3")) _
> Is Nothing Then
> Me.Cells(Me.Range("A:A").Rows.Count, Target.Column).Clear
> Dim rgOldValues As Range
> Dim iLastRow As Long
> iLastRow = Me.Cells(Columns(Target.Column).Rows.Count, Target.Column)
> _
> .End(xlUp).row
> Application.EnableEvents = False
> Select Case iLastRow
> Case 1
> Case 2
> Case 3
> Range("A4:H4").Value = Range("A3:H3").Value
> Range("C4").Value = Now
> Cells(4, Target.Column).Value = Cells(3, Target.Column).Value
> Case Else
> vaOldValues = Me.Range("A4:H" & _
> IIf(iLastRow = 4, 5, iLastRow))
> Range("A5:H5").Resize(UBound(vaOldValues, 1), 6).Value = _
> vaOldValues
> Range("A4:H4").Value = Range("A3:H3").Value
> Range("C4").Value = Now
> Set rgOldValues = Me.Range(Cells(Target.row + 2, Target.Column), _
> Cells(iLastRow, Target.Column))
> Cells(4, Target.Column).Value = Cells(3, Target.Column).Value
> End Select
> Application.EnableEvents = True
> End If
>
> End Sub
>
>
> --
> mikespeck
> ------------------------------------------------------------------------
> mikespeck's Profile: http://www.excelforum.com/member.php...o&userid=34946
> View this thread: http://www.excelforum.com/showthread...hreadid=569464
>
>