Format Dates

[silentwolf]

Hi guys,

I came accross this code from Norie and it works fine!

However I like to format the dates in Wednesday 20 Feb 2019 and would love to have sundays automatically in red.

Sub FillDates()
    Dim StartDate As Date
    Dim EndDate As Date
    Dim NoDays As Integer

    With tabAuflistung
        StartDate = Range("B2").Value
        EndDate = Range("B3").Value
        
        NoDays = EndDate - StartDate + 1
        Range("A7").Value = StartDate
    
        Range("A7").Resize(NoDays).DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:= _
            xlDay, Step:=1, Stop:=EndDate, Trend:=False

    End With
End Sub

I tried with format function but had no luck so far.

Help would be much appreciated!

Many Thanks

Albert

 

[footoo]

Sub FillDates()
Dim StartDate As Date
Dim EndDate As Date
Dim NoDays As Integer
Dim cel As Range


'With tabAuflistung
    With Range([A7], [A7].End(xlDown))
        .Font.ColorIndex = xlAutomatic
        .ClearContents
    End With


    StartDate = Range("B2").Value
    EndDate = Range("B3").Value
    
    NoDays = EndDate - StartDate + 1
    Range("A7").Value = StartDate


    With Range("A7").Resize(NoDays)
        .DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:= _
            xlDay, Step:=1, Stop:=EndDate, Trend:=False
        .NumberFormat = "dddd dd mmm yyyy"
    End With


    For Each cel In Range("A7").Resize(NoDays)
        If Weekday(cel) = 1 Then cel.Font.Color = 255
    Next
'End With
End Sub

Note :
The lines With tabAuflistung...End With​ are not doing anything and have been commented out in the code.

 

[silentwolf]

Hi Footoo,

Many thanks for your help!
Works great!

Cheers