(Solved) – How to merge two “Byval target as range”


Hope you can assist me on how it is possible to merge these two macros into one? Both Macros work fine independently but when i try to merge them one of them stops working. i have tried so many things but none of them seem to work.

The first macro is shown below and automatically inserts and copy the above row when the user click a specific range of cells. Furthermore columns width is automatically adjusted and formatting for a column is changed.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
    If Not Intersect(ActiveCell, Range("E15:E45")) Is Nothing Then
    With Selection
    .Offset(1, 0).EntireRow.Insert Shift:=xlShiftDown

End With
    End If

       'Automatically autofit columns when changes

    'Format columns 9 (I) as TEXT
    Columns(9).NumberFormat = "@"

End Sub

The second macro automatically inserts company name and project name in two columns when end users inserts the project code. Hence, it is a basically an automated lookup insert.

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

    If Not Intersect(Range("H:H"), Target) Is Nothing Then
        For Each cell In Intersect(Range("H:H"), Target)
            cell.Offset(0, 1).Interior.ColorIndex = xlNone
            If cell <> "" Then Call macro2(cell)
        Next cell
    End If
End Sub

Sub macro2(T As Range): Dim F As Range, w2 As Worksheet
Set w2 = Sheets("Lookups")
Set F = w2.Range("H:H").Find(T.Value, , , xlWhole)
If Not F Is Nothing Then
T.Offset(0, 1) = F.Offset(0, 1)
T.Offset(0, -1) = F.Offset(0, 2)
Else: T.Offset(0, 1).Interior.ColorIndex = 3: T.Offset(0, 1) = ""
End If
End Sub

Hope you can help 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *