Vba rename file when cell of another workbook is updated

[Mariec_06]

I have a file "cargoes 2016" as a database where the datas are sorted per "REF"(column "C"), I would like that when the cell of the colum "AI" is updated for a specific "REF", it goes to a specific folder and rename automatically the file whose name include "ref". However being totally new in vba, I don't know how to refer to the good cells/column.

Basically, if I update the cell of the column "AI" (=date) in the row of ref "2016xx001", the file in the specific folder who includes in its name "2016xx001" is automatically renamed (without opening)

what I did until now is create a module with the action "rename file" and insert code under the worksheet that if cell ai is updated, "rename file" macro runs

Here below both codes

Sub RENAME_FILES()
Dim OldName As String
Dim NewName As String
Dim REF As String

Directory = "\\192.168.2.6\elenalouroutziati_document\B R E A K D O W N S\BREAKDOWNS 2016\"

Filename = Dir("Directory" & "*" & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("C").Value & "*" & ".xlsm")

OldName = "directory" & "CALC(P) - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("C").Value & " - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("L").Value & " - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("M").Value & " + " & "CALC(S) - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("W").Value & " - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("X").Value & " - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("F").Value & ".xlsm"

NewName = "directory" & "CALC(P) - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("C").Value & " - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("L").Value & " - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("M").Value & " + " & "CALC(S) - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("W").Value & " - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("X").Value & " - " & Workbooks("CARGOES 2016").Worksheets("RECAP").Range("F").Value & Format(Workbooks("CARGOES 2016").Worksheets("RECAP").Range("AI").Value, "dd-mmm-yy") & ".xlsm"


Name OldName As NewName

End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Irow As Long

Irow = Workbooks("CARGOES 2016").Worksheets("RECAP").Cells(Rows.Count, "A").End(xlUp).Row

    If Not Intersect(Workbooks("CARGOES 2016").Worksheets("RECAP").Cells(Irow, "AI"), Target) Is Nothing And Workbooks("CARGOES 2016").Worksheets("RECAP").Cells(Irow, "AI") <> "" Then
      
      Application.Run "RENAME_FILES"
      
    End If
End Sub

Please note that my table has around 100 rows, each "ref" being "2016xx001", 2016xx002, 2016xx003 etc

so if I update the column AI of the ref 2016XX003, then my code goes to find the file which includes "2016xx003" in its title, and rename it adding at the end of the name the column Ai value....

I know my problem is how to refer to the good cell....can someone help?