Running a code based on mouse selected range?

[jimayers]

Hello - I am trying to figure out how to run a code based on multiple cells selected by mouse.
I have been trying some basics to figure it out with no success:

Sub Color_Green()
Application.ScreenUpdating = False
Dim Ac As Range
Dim a, b As Long

With ActiveSheet
    Set Ac.Range = Selection.Address
    Range(a, b) = Ac
    MsgBox (Ac)
    MsgBox (a)
    MsgBox (b)
End With
Exit Sub

Can anyone help - Thanks - Jim A

 

[jayesh25]

Perhaps something like this in your sheet?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'check if multiple cells are selected




If VBA.InStr(1, Target.Address, ":") <> 0 Then


'run your code here


MsgBox "Multiple cells were selected"


End If




End Sub

 

[jimayers]

I need to run code on each cell selected by the mouse, which is likely going to be in the same column.

 

[jayesh25]

Can you demonstrate what code do you intend to run on each cell? For example if user selects Range A1 with mouse do you want to do something? Or if he selects Range ("A1:A2") with mouse then you want to do something to the cells?

 

[Eric W]

I think this may be the type of thing you're looking for:

Sub Color_Green()
Dim cel As Range

    For Each cel in Selection
        Debug.Print cel.Address, "=", cel.Value
    Next cel

Exit Sub

 

[jimayers]

I think this may be the type of thing you're looking for:

Sub Color_Green()
Dim cel As Range

    For Each cel in Selection
        Debug.Print cel.Address, "=", cel.Value
    Next cel

Exit Sub

Yes...exactly - thanks
PS, I did have to use "cel" inside my If Then statements

 

[Eric W]

I just pointed out a technique I thought you could use. I'm glad you were able to adapt it to your situation!