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

(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
    .EntireRow.Copy
    .Offset(1, 0).EntireRow.Insert Shift:=xlShiftDown

End With
    End If

       'Automatically autofit columns when changes
    ThisWorkbook.Worksheets("Load").Range("F:N").EntireColumn.AutoFit

    '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 *