VBA to run if any cell within already selected range is clicked

[Nightlytic]

Could anyone tell me if this is possible and offer some guidance on what I can use? Essentially, say I want to colour all cells of a selected range, if I click into a cell that is within that range.

To illustrate:
-Range A1:A3 is selected (3 cells)
If:
-user selects range A5 (1 cell) nothing happens
-user selects range B1:B3 (3 cells) nothing happens
-user selects range A2:A3 (2 cells) nothing happens
-user selects range A1 (1 cell) macro executes to colour earlier selected range A1:A3 green
-user selects range A2 (1 cell) macro executes to colour earlier selected range A1:A3 green
-user selects range A3 (1 cell) macro executes to colour earlier selected range A1:A3 green

And in a situation where user selects cell A8, then clicks onto A8 again, colour just A8

 

[shrivallabha]

- Are A1:A3 & A8 cells only the ones which need coloring?
- What happens to A1:A3 & A8 cell colors when user selects some other cells?

It should be possible to handle this using Worksheet_SelectionChange event.

 

[Nightlytic]

- Are A1:A3 & A8 cells only the ones which need coloring?
- What happens to A1:A3 & A8 cell colors when user selects some other cells?

It should be possible to handle this using Worksheet_SelectionChange event.

-No, any given selection within a specific column
-Nothing happens, colour stays the same

The colour setting is just a simplified version of my problem, essence of which is to how to trigger a macro, any macro, to run on selected cells, if a single cell within those selected cells is clicked. I will give you a sample, my current macro is there which does the exact job I require- if you select a range, or multiple ranges in column D, and press the button, the reference for the companies is saved down in column K. Alongside that some other events are triggered, a flag for example is flicked from 0 to 1 on another sheet if all companies within a selected industry were picked, or a colour is added to the cells to indicate that they were picked (though I might do that with conditional formats instead)

Except it only works with a button or shortcut, as you say I would need some sheet event to run the macro if a selection is confirmed with a click, into any single already selected cell. I'm trying to come up with the quickest possible way for someone to evaluate a very large dataset and essentially toss things in and out by selecting ranges and clicking

 

[NARAYANK991]

Hi ,

See if this helps :

https://www.experts-exchange.com/qu...-double-click-and-a-right-click-in-Excel.html

Narayan

 

[Chihiro]

Something like this? It's just a demo on coloring, old selection.
In Worksheet module.

Dim oRange As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If oRange Is Nothing Then
        Set oRange = Target
        Exit Sub
    ElseIf oRange.Cells.Count = 1 Then
        Set oRange = Target
    End If
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, oRange) Is Nothing Then
        Call ColorMe(oRange)
        Set oRange = Nothing
    Else
        Call UnColorMe(oRange)
        Set oRange = Nothing
    End If
End Sub

In Standard module.

Sub ColorMe(mRange As Range)
    mRange.Interior.Color = vbRed
End Sub
Sub UnColorMe(mRange As Range)
    mRange.Interior.ColorIndex = xlNone
End Sub

Play with it and see if it can be applied to your situation.
I haven't fully tested out the logic.

Note that if you select entire sheet, it will likely cause overflow error.

Edit: Though I really don't recommend this approach to user interaction.

 

[Nightlytic]

Something like this? It's just a demo on coloring, old selection.
In Worksheet module.

Dim oRange As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If oRange Is Nothing Then
        Set oRange = Target
        Exit Sub
    ElseIf oRange.Cells.Count = 1 Then
        Set oRange = Target
    End If
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, oRange) Is Nothing Then
        Call ColorMe(oRange)
        Set oRange = Nothing
    Else
        Call UnColorMe(oRange)
        Set oRange = Nothing
    End If
End Sub

In Standard module.

Sub ColorMe(mRange As Range)
    mRange.Interior.Color = vbRed
End Sub
Sub UnColorMe(mRange As Range)
    mRange.Interior.ColorIndex = xlNone
End Sub

Play with it and see if it can be applied to your situation.
I haven't fully tested out the logic.

Note that if you select entire sheet, it will likely cause overflow error.

Edit: Though I really don't recommend this approach to user interaction.

Wow that works ! Thank you Chihiro, only thing it does is that if I select 1 cell and then select a different (1) cell it colours the new selection

 

[Nightlytic]

I think it's this bit:

  ElseIf oRange.Cells.Count = 1 Then

        Set oRange = Target

    End If

it lets the oRange = Target without exiting the sub, and they naturally intersect one another further down in the code despite the fact that the selection was different?

 

[Chihiro]

Correct, just add Exit Sub routine.