PDA

View Full Version : [SOLVED] Intersect - very basic problem



raytrace
12-28-2013, 01:52 AM
I am trying to use Intersect to detect change in a certain range.
I applied the code to the Worksheet's "Worksheet_SelectionChange" event.
The code seems to works perfectly save for one glitch that I cannot seem to sort out.

Whenever I try to select multiple rows and columns overlapping the given range (C3:C30) that Intersect works fine.
It even works perfectly if I select only one cell inside the range (e.g C10).
However, when I select a single row (e.g B10:D10) the Intersect does not recognize it as anything.



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim watchrange2 As Range
Dim isecRange As Range

Set watchrange2 = Range("C3:C30") 'same as in CHANGE WORKSHEET event
'check if currentlyselected area has any overlapping cell in wathrange range
Set isecRange = Application.Intersect(Target, watchrange2)

If isecRange Is Nothing Then
'do nothing
Else
If isecRange.Rows.Count > 1 Or isecRange.Columns.Count > 1 Then
'selected area is intersecting the watched range, and has either rows or columns exceeding 1
'|--> stop it right here
MsgBox ("Target (range):" & Target.Address & ", isecRange=" & isecRange.Address)
Target(1, 1).Select
Else
OldVal = Target.Value 'to retain value pre-change
End If
End If
End Sub

SamT
12-28-2013, 01:47 PM
Raytrace,

You marked the thread solved. did you figure it out?

Will you share the solution with us?

raytrace
01-20-2014, 02:38 AM
Raytrace,

You marked the thread solved. did you figure it out?

Will you share the solution with us?

Actually, it was a careless mistake that I was making.
The code was working perfectly. The part where Rows.Count is being checked ( isecRange.Rows.Count > 1) was supposed to be >0, not great that one. All I did was to change the 1 to zero.

Sometimes I think I need to drink less caffeine :think: