MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Coding Problems

  Asked By: Navin    Date: Sep 05    Category: MS Office    Views: 1101

I am having a bit of difficulty with some coding in
visual basic. Below I have some code (Current Code)which works fine,
but I want to add an additional function (Additional Code), and I
cannot get it to work. I am probably overlooking something simple and

Current Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(ActiveCell, Range("B14:B33")) Is Nothing Then
Range("M3") = Target.Value
Range("M4") = Target.Offset(, 1).Value
Sheet30.Range("B7") = Target.Value
End If
End Sub

Additional Code:
If Not Intersect(ActiveCell, Range("M4")) Is Nothing Then
Range("C14:C33") = Target.Value



2 Answers Found

Answer #1    Answered By: Muntasir Bashara     Answered On: Sep 05

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Me.Range("B14:B33")) Is Nothing Then
Me.Range("M3") = Target.Value
Me.Range("M4") = Target.Offset(, 1).Value
Sheet30.Range("B7") = Target.Value
ElseIf Not Intersect(Target, Me.Range("M4")) Is Nothing Then
Me.Range("C14:C33") = Target.Value
End If
End Sub

Answer #2    Answered By: Cadencia Bernard     Answered On: Sep 05

I'm surprised that the Intersect(ActiveCell,Range("B14:B33")) works.
Because the default Property for ActiveCell is ActiveCell.Value, not
ActiveCell.Address... But evidently, Excel does some funny things..

Anyway, you don't have to use ActiveCell because the whole purpose
of the macro is to pass the Selected Cell as the "Target" range variable.
To use it, use:
If (not intersect(Target.Address,Range("B14:B33"))) then
If (not intersect(Target.Address,Range("M4"))) then
I tested this, and it seems to work  as *I* intended it to work.
What I don't know is if it is how YOU want it to work.
(meaning: if you select a range of cells that INCLUDES M4, it will
not indicate a "match", but if M4 is selected by itself, it works).

I guess to be more helpful, we'd have to know how you want this to operate.

Didn't find what you were looking for? Find more on Coding Problems Or get search suggestion and latest updates.