Working with Odd and Even rows in coding

[kosherboy]

Hello everybody,
The code below checks if the left cell, which is 11 cells to the left of the target cell, is duplicated anywhere in column L and then copies the value in the target cell to every cell on column W that the value of the cell that is 11 cells to the left is matching the value of the target's 11 cells to the left.
The code is currently copying the valueinto every ODD row, i would like the code to copy the value into every EVEN row. How can i go about doing this?

If (Target.Row > 4) And (Target.Count = 1) And (Target.Column = 23) And (Target.Row Mod 2 = 0) Then
If Not Target.Offset(-1, -11) = "N/A" Then
     Application.EnableEvents = False
     Application.ScreenUpdating = False
     Application.Calculation = xlManual

     Dim mtx4(), CurrCell4, LeftCell4, i4 As Long
     'Matrix of columns L:W
     mtx4 = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("L:W")).Value
     'Value of current cell
     CurrCell4 = Target.Value
     'Value of the cell left of current cell
     LeftCell4 = Target.Offset(-1, -11).Value
     'Comparing and set if matched
     For i4 = 1 To UBound(mtx4, 1)
         If mtx4(i4, 1) = LeftCell4 Then mtx4(i4, 12) = CurrCell4
     Next i4
     
     'Write matrix back to range L:W
     Range("L1").Resize(UBound(mtx4, 1), UBound(mtx4, 2)).Value = mtx4

     Application.EnableEvents = True
     Application.ScreenUpdating = True
     Application.Calculation = xlCalculationAutomatic
End If
End If

It would be absolutely amazing if this issue can get resolved!

[xladept]

Hi Kosherboy,

I notice that:

Target.Offset(-1, -11).Value

is the previous row as well as 11 to the left?? Hope that helps

[kosherboy]

Hi xladept,
I need the code to read the value of the cell that the row is on top of the target row and 11 cells to the left 9of the target cell). Therefore the value in Leftcell4 should equal the value of the target cell minus 1 row 9up) and minus 11 columns (to the left).

I need that the missing part of the code to do the following: The code needs to use the leftcell4 value and offset 11 cells to the right and 1 cell down of leftcell4 and then insert the value of Currcell4.

[xladept]

I need to see all the code

[kosherboy]

Here is an example workbook with the code EXAMPLE.xlsm
The yellow boxes need to be all filled out automatically when any of the yellow boxes have value entered in it. My code is writing the value in the green boxes as opposed to the yellow.
So my goal is that if i enter a "Y" in the first yellow box i want the other yellow boxes to be filled out automatically. This code is working based off the matching "PO" numbers on column L.

Regards,
Kosherboy

[kosherboy]

I GOT IT!
See below blue font that i added to the code

Private Sub Worksheet_Change(ByVal Target As Range)

    If (Target.Row > 4) And (Target.Count = 1) And (Target.Column = 23) And (Target.Row Mod 2 = 0) Then
        If Not Target.Offset(-1, -11) = "N/A" Then
        
            Application.EnableEvents = False
            Application.ScreenUpdating = False
            Application.Calculation = xlManual
        
                Dim mtx4(), CurrCell4, LeftCell4, i4 As Long
                'Matrix of columns L:W
                mtx4 = Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("L:W")).Value
                'Value of current cell
                CurrCell4 = Target.Value
                'Value of the cell left of current cell
                LeftCell4 = Target.Offset(-1, -11).Value
                'Comparing and set if matched
                For i4 = 1 To UBound(mtx4, 1)
                    If mtx4(i4, 1) = LeftCell4 Then mtx4(i4 + 1, 12) = CurrCell4
                Next i4
                
                'Write matrix back to range L:W
                Range("L1").Resize(UBound(mtx4, 1), UBound(mtx4, 2)).Value = mtx4
        
            Application.EnableEvents = True
            Application.ScreenUpdating = True
            Application.Calculation = xlCalculationAutomatic
            
        End If
    End If

End Sub

Solved my own thread

[xladept]

Hey KosherBoy,

Well done - I was looking at that index

Thanks for the rep!