Format Dates

silentwolf

Well-known Member
Joined
May 14, 2008
Messages
1,123
Office Version
  1. 2016
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.

Code:
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
 
Last edited:

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,055
Office Version
  1. 2016
Platform
  1. Windows
Code:
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

Well-known Member
Joined
May 14, 2008
Messages
1,123
Office Version
  1. 2016
Hi Footoo,

Many thanks for your help!
Works great!

Cheers
 

Watch MrExcel Video

Forum statistics

Threads
1,114,645
Messages
5,549,167
Members
410,903
Latest member
natesreich
Top