Thread: [Solved] Need Help Creating a Macro
View Single Post
 
Old 09-19-2019, 08:41 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 759
NoSparks has a spectacular aura aboutNoSparks has a spectacular aura about
Default

Give this a shot, if the coloring of the date cells was just for emphasis and not needed,
remove the variable from the declarations and the two color lines in the macro.
Code:
Sub rsrasc()
    Dim i As Long, ws As Worksheet
    Dim rng As Range, cel As Range
    Dim lr As Long, dte As Date, colour As String
    
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For i = 1 To Sheets.Count
    With Sheets(i)
        dte = .Range("A4").Value
        colour = .Range("A4").Interior.Color
        lr = .Range("B" & .Rows.Count).End(xlUp).Row
        For Each cel In .Range("C6:C" & lr)
            If cel.Offset(, -1) <> "" Then
                With cel
                    .NumberFormat = "d-mmm-yy"
                    .Interior.Color = colour
                    .Value = dte
                End With
            End If
        Next cel
    End With
Next i

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub
Reply With Quote