Active Work Order Board. Need VBA

[GraphWS]

Everything works as is BUT
Right now I open each file hyperlinked in Column A select cells C18 to O18 of that file and drag right from Column C to Column O of ActiveWorkOrders file.

 =[AWOF10428.xlsX]Sheet1!$C$18:$O$18

The files hyperlinked in Column A are files people can update using online version of Excel by putting a X in a category as they are completed or not required.
I use the below Sub to set the conditional formatting on 30 rows. The number of rows is going to grow fast when we get out of “testing phase” lol

Sub AssignFormat()
 
With ThisWorkbook.Worksheets(1).Range("C3:O3")
     .FormatConditions.Delete
     .FormatConditions.Add Type:=xlExpression, Formula1:="=COUNTIF($C3:C$3,C3)=1"
     .FormatConditions(1).Interior.ColorIndex = 3
     .FormatConditions(1).Font.ColorIndex = 3
End With

I used VBA to set conditional formatting because for some reason the rules would change seemly on their own.
I use a Power Query on Sheet2 to update data in Column A & B.
What I’d like to have is VBA that would fill in link info in each row. Files are and always will be in same dropbox folder. Names will always start with “AWOF” followed by number(same number in column A).
While I’m wishing on a star I’d also like have hyperlink and number in Column A copied to which ever cell in row is red (first 0) moving towards Close & Bill.

Thanks

 

[GraphWS]

Trying to post a couple images to help explain but they didn't come thru