Run Macro From Row Selection ?

[JLouis]

I have this code from Mumps that uses a list of names on one sheet to find the name on another WS and hide the row. I am now trying to modify the code to select a specific range of cells after the row number has been determined from the line:

    desWS.Rows(dic(v2(i, 1))).Hidden = True

In my test WB, desWS.Rows(dic(v2(i, 1))) returned row 4 as an example. I now want to select the range in row 4 "a4:i4" so I can run a routine on that range.. I've come close to setting a variable for "desWS.Rows(dic(v2(i, 1)))" but I'm not using the right syntax. Am I on the right path here or do I need to start from scratch?
Thanks for looking!

Sub HideRows()
    Application.ScreenUpdating = False 
    Dim srcWS As Worksheet, desWS As Worksheet, v1 As Variant, v2 As Variant, i As Long, dic As Object
    Set srcWS = Sheets("Sheet2")
    Set desWS = Sheets("Sheet1")
    v1 = desWS.Range("A1", desWS.Range("A" & Rows.Count).End(xlUp)).Value
    v2 = srcWS.Range("A1", srcWS.Range("A" & Rows.Count).End(xlUp)).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v1) To UBound(v1)
        If Not dic.exists(v1(i, 1)) Then
            dic.Add v1(i, 1), i
        End If
    Next i
    For i = LBound(v2) To UBound(v2)
        If dic.exists(v2(i, 1)) Then
            desWS.Rows(dic(v2(i, 1))).Hidden = True
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[mumps]

This code assigns A4:I4 to the variable rng.

Sub HideRows()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, desWS As Worksheet, v1 As Variant, v2 As Variant, i As Long, dic As Object, rng As Range
    Set srcWS = Sheets("Sheet2")
    Set desWS = Sheets("Sheet1")
    v1 = desWS.Range("A1", desWS.Range("A" & Rows.Count).End(xlUp)).Value
    v2 = srcWS.Range("A1", srcWS.Range("A" & Rows.Count).End(xlUp)).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v1) To UBound(v1)
        If Not dic.exists(v1(i, 1)) Then
            dic.Add v1(i, 1), i
        End If
    Next i
    For i = LBound(v2) To UBound(v2)
        If dic.exists(v2(i, 1)) Then
            Set rng = desWS.Range("A" & dic(v2(i, 1))).Resize(, 9)
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[JLouis]

Hi Mumps! Happy new year!

So I added the macro I wanted to run with the code, but it only works on whatever cell I had selected when the code is run. Please review below and advise.

PS: "ARedLine" is just cell formatting of the selected cells starting with:

   With Selection.Font...

Sub anewone()
    Application.ScreenUpdating = False
    Dim srcWS As Worksheet, desWS As Worksheet, v1 As Variant, v2 As Variant, i As Long, dic As Object, rng As Range
    Set srcWS = Sheets("Sheet2")
    Set desWS = Sheets("Sheet1")
    v1 = desWS.Range("A1", desWS.Range("A" & Rows.Count).End(xlUp)).Value
    v2 = srcWS.Range("A1", srcWS.Range("A" & Rows.Count).End(xlUp)).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v1) To UBound(v1)
        If Not dic.exists(v1(i, 1)) Then
            dic.Add v1(i, 1), i
        End If
    Next i
    For i = LBound(v2) To UBound(v2)
        If dic.exists(v2(i, 1)) Then
            Set rng = desWS.Range("A" & dic(v2(i, 1))).Resize(, 9)
          '  MsgBox rng
        With rng
        .Application.Run "Book1!ARedLine"
        End With
        End If
     '   MsgBox i
    Next i
    Application.ScreenUpdating = True
End Sub

 

[JLouis]

I fixed it, I fixed it!

I added the code below and it worked perfectly.

        If dic.exists(v2(i, 1)) Then
            Set rng = desWS.Range("A" & dic(v2(i, 1))).Resize(, 9)
            rng.Select
        With Selection
      .Application.Run "Book1!ARedLine"
        End With

Thanks again for all your help! You're awesome!

 

[mumps]

You are very welcome.