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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,050
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,109,456
Messages
5,528,864
Members
409,842
Latest member
mfernandezcean
Top