Dynamic Hyperlink Loop All Sheets

[Vbalearner85]

Hi ..I have below the macro, which created dynamic hyperlinks via looping in all sheets of the workbook(some 150 tabs)..

Column BN is where hyperlinks are created; based upon the condition that time >equivalent cell in column BA (else blank the target cell)
Column B is source data where hyperlinks of column BN should refer to.

It is working fine but is too slow...Any optimization edits are welcome. Also, is it possible to not use formulas in cells and still create hyperlinks.(as multiple formulas are making files slow)?

Regards,
VbaLearner85

Sub CLL()
    Dim xSh As Worksheet
    Application.ScreenUpdating = False
    For Each xSh In Worksheets
        xSh.Select
        Call RunCode
    Next
    Application.ScreenUpdating = True
End Sub
Sub RunCode()
    'Creating Hyperlinks
  
Dim LastRow As Long
Dim rng As Range, C As Range
Range("BA2").Select     'Column BA has source conditional data  for which hyperlink is needed--refer below
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("BN2").Select    'Copying Column BA data to column BN..to use for a condition for inputting hyperlink Formula in each cell
ActiveSheet.Paste
Range("BN1").Select

With ActiveSheet
    LastRow = .Cells(.Rows.Count, "BN").End(xlUp).Row
    Set rng = .Range("BN2:BN" & LastRow)
    For Each C In rng
        If C.Value2 > ActiveSheet.Range("$BZ$1").Value2 Then      'creating hyperlinks via formula in col BN if cell value is > Range("$BZ$1")..which is already input as "00:00:30"
           C.FormulaR1C1 = "=HYPERLINK(""#""&CELL(""address"",INDEX(r2c2:r25000c2,MATCH(rc52,r2c2:r25000c2,0))),TEXT(rc52,""hh:mm:ss""))"
        ElseIf C.Value2 < Sheets("Index").Range("$BZ$1").Value2 Then
            C.Value = " "
        End If
    Next C
End With
ActiveSheet.Calculate

End Sub

 

[Vbalearner85]

Any help for above macro please