Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2006
    Posts
    162

    Unanswered: Run macro on cell selection.

    How do I run a macro when a certain cell is selected. FYI there are 20 sheets with 3-4 cells on each that will need this macro.

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    You can use a module in the Worksheet "SelectionChange". Right click on the worksheet and choose "View Code". Paste this code into the VBE. Do this for each worksheet where you need it done.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim rng As Range
        Set rng = Intersect(Target, Range("B3"))
        If rng is Nothing Then
            Exit Sub
        Else
            Dim cl as Range
            For Each cl In rng
               .....
            Next cl
        End If
    End Sub
    Instead of having just Range("B3"), you could use Union of several cells, and then it would be combined into one.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Sep 2006
    Posts
    162
    For some reason I couldn't get your code to work. But from looking at your and researching other options I came up with this.



    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        If Target.Cells.Count = 1 Then
            If Target.Address = "$L$1" Then
                Macro
            ElseIf Target.Address = "$L$2" Then
                Macro
            ElseIf Target.Address = "$L$3" Then
                Macro
            End If
        Else
        End If
    End Sub

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Mine was just an example, and notice I didn't have any actions listed, just the three dots to indicate action code to go there.

    As long as yours works, that's what counts. Glad it did what you want.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Mar 2006
    Posts
    163
    If you want this to work on multiple sheets you would probably be better of using a workbook event, eg SheetSelectionChange.
    Code:
    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    
    End Sub
    Note it's passed two parameters Sh and Target.

    Sh is the sheet that the selection change has happened and Target is the new selection.

  6. #6
    Join Date
    Feb 2012
    Posts
    2

    Try the below & let me know...

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Column = 1 Then
    ThisRow = Target.Row
    If Target.Value > 100 Then
    Range("B" & ThisRow).Interior.ColorIndex = 3
    Else
    Range("B" & ThisRow).Interior.ColorIndex = xlColorIndexNone
    End If
    End If
    End Sub

  7. #7
    Join Date
    Feb 2012
    Posts
    2

    Try the below if u r not able to check with the above...

    Try the below if u r not able to check with the above...
    If any thing else let me know...

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range

    ' The variable KeyCells alerts when ever they are changed.
    Set KeyCells = Range("A1:C10")

    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
    Is Nothing Then

    MsgBox "Cell " & Target.Address & " has changed."
    'this line displays the required message

    End If
    End Sub

    If any thing else let me know...
    Regards...
    Subbu

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •