add a range to this VB

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

how do i add a range of cells to this to add the same effect to another row?
presently it is d23:p23 I would like to add d29:p29


Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim CheckRange As Range
Set CheckRange = Range("d23:p23")


If Union(CheckRange, Target).Address = _
Union(CheckRange, CheckRange).Address Then

If Target.Cells(1).Value > 0 Then
For Each Cell In Target.Cells
Cell.Value = Cell.Value * -1
Next Cell
End If
End If

End Sub
 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim CheckRange As Range
Set CheckRange = Range("d23:p23,D29:P29")

If Union(CheckRange, Target).Address = _
Union(CheckRange, CheckRange).Address Then

If Target.Cells(1).Value > 0 Then
For Each Cell In Target.Cells
Cell.Value = Cell.Value * -1
Next Cell
End If
End If

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
thanks Bob...
well I tried that and strangely, the stuff in row 23 goes back to regular
numbers but the added row 29 works nicely....
any ideas?
 
Here's another shot Dave (tested this time)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim CheckRange As Range
Set CheckRange = Union(Range("d23:p23"), Range("D29:P29"))

If Not Intersect(Target, CheckRange) Is Nothing Then

If Target.Cells(1).Value > 0 Then
For Each Cell In Target.Cells
Cell.Value = Cell.Value * -1
Next Cell
End If
End If

End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
thanks Bob! that did it.... I tried every combo I could think of , but not
THAT one.
thanks again
 
Dave,

You don't actually need the first union

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim CheckRange As Range
Set CheckRange = Range("d23:p23", "D29:P29")

If Not Intersect(Target, CheckRange) Is Nothing Then

If Target.Cells(1).Value > 0 Then
For Each Cell In Target.Cells
Cell.Value = Cell.Value * -1
Next Cell
End If
End If

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
thanks .... how is Poole these days!?


Bob Phillips said:
Dave,

You don't actually need the first union

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim CheckRange As Range
Set CheckRange = Range("d23:p23", "D29:P29")

If Not Intersect(Target, CheckRange) Is Nothing Then

If Target.Cells(1).Value > 0 Then
For Each Cell In Target.Cells
Cell.Value = Cell.Value * -1
Next Cell
End If
End If

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
Click to expand...
 
Hi Dave,

Poole is bright, busy and prosperous. It is quite dynamic these days.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top