Replace adjacent cells with value within IF statement

[apo]

HI..

I have made the following macro to clean up some data...

It successfully replaces "GR" with "KG" in the required place...

The thing i need help with is that I need (within that IF statement) .. it to also replace the adjacent cells (i,10) and (i,11) with "1" and "KG" respectively..

I'm stuck on this.. any help will be appreciated.. thanks

Private Sub CommandButton1_Click()


Dim LR As Long, i As Long


Application.ScreenUpdating = False
LR = Range("I" & Rows.Count).End(xlUp).Row


For i = 1 To LR
If Right(Cells(i, 9).Value, 2) = "GR" And Cells(i + 1, 11).Value = "GR" Then
 


    With Range("I" & i)
        If Right(.Value, 2) = "GR" Then .Value = Left(.Value, Len(.Value) - 2) & "KG"
        
    End With
    
Application.ScreenUpdating = True


End If
    
Next i
   
End Sub

 

[patel45]

Private Sub CommandButton1_Click()
Dim LR As Long, i As Long
Application.ScreenUpdating = False
LR = Range("I" & Rows.Count).End(xlUp).Row
For i = 1 To LR
   s=Cells(i, 9).Value
   If Right(s, 2) = "GR" And Cells(i + 1, 11).Value = "GR" Then  
       Cells(i, 9).Value = Left(s, Len(s) - 2) & "KG"
       Cells(i, 10).Value = "1"
       Cells(i, 11).Value = "KG"
   end if
Next i
Application.ScreenUpdating = True  
End Sub

 

[apo]

Big thanks patel45... that's exactly what i needed..