VBA code mix

[Agnarr]

Hello everyone!
I have the following code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    
    ' Check if column G is changed
    If Not Intersect(Target, Sh.Range("G:G")) Is Nothing Then
        Application.ScreenUpdating = False
        If Target.Value = "" Then
            ' Clear cells B, C, D, E, and F
            Target.Offset(0, -5).Resize(1, 5).ClearContents
        Else
            Dim fnd As Range
            Set fnd = Sheets("codes").Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
            If Not fnd Is Nothing Then
                ' Copying name and original price
                Target.Offset(0, -5).Value = fnd.Offset(0, 1).Value
                Target.Offset(0, -3).Value = fnd.Offset(0, 2).Value
                
                ' Calculating the price based on quantity
                Dim quantity As Double
                If IsEmpty(Target.Offset(0, -2).Value) Then
                    quantity = 1
                Else
                    quantity = Target.Offset(0, -2).Value
                End If
                Target.Offset(0, -4).Value = Target.Offset(0, -3).Value * quantity
            End If
        End If
        Application.ScreenUpdating = True
    End If
    
    ' Check if column E is changed
    If Not Intersect(Target, Sh.Range("E:E")) Is Nothing Then
        Application.ScreenUpdating = False
        Dim rng As Range
        For Each rng In Target
            ' Get the corresponding row in column C
            Set cCell = Sh.Cells(rng.Row, "C")
            
            ' Recalculate the value in column C based on the formula in your sheet
            cCell.Value = Application.Evaluate("=IF(E" & rng.Row & "="""",D" & rng.Row & ",D" & rng.Row & "*E" & rng.Row & ")")
        Next rng
        Application.ScreenUpdating = True
    End If
End Sub

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim ws As Worksheet
    Set ws = Sh
    Call Workbook_SheetChange(ws, ws.Cells(1, 1)) ' Trigger the code for the new sheet
End Sub

This works perfectly for what I need it to.
My problem is that in another workbook i want the same code to exist for two different columns. The code so far only shows product name and price. I don't seem to be able to fix the rest though. Please help me guys.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim rng As Range
    Set rng = Intersect(Target, Sh.Range("D:D, L:L"))
    
    If rng Is Nothing Then Exit Sub ' Exit if no overlapping cells
    
    If rng.Cells.CountLarge > 1 Then Exit Sub
    
    Application.EnableEvents = False ' Disable events
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual ' Set calculation to manual
    
    Dim fnd As Range
    Dim cell As Range
    
    On Error Resume Next ' Ignore errors temporarily
    
    For Each cell In rng
        Set fnd = Sheets("codes").Range("A2:A20000").Find(cell.Value, LookIn:=xlValues, LookAt:=xlWhole)
        If Not fnd Is Nothing Then
            cell.Offset(0, -2).Value = fnd.Offset(0, 1).Value
            cell.Offset(0, -1).Value = fnd.Offset(0, 2).Value
            
            
        End If
    Next cell
    
    On Error GoTo 0 ' Reset error handling
    
    Application.Calculation = xlCalculationAutomatic ' Reset calculation to automatic
    Application.ScreenUpdating = True
    Application.EnableEvents = True ' Re-enable events
End Sub

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim ws As Worksheet
    Set ws = Sh
    Call Workbook_SheetChange(ws, ws.Cells(1, 1)) ' Trigger the code for the new sheet
End Sub

 

[Joe4]

Many times, it can be hard to visualize what you are trying to do just by looking at code with no data.
I think it might be beneficial if you could post a sample of your current data, along with the expected result of what you want it to look like after the code runs.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.