VBA that distributes dates along the year based on a schedule

[Daniel Kimathi]

Hi,
How can I create a VBA for a preventive maintenance that distributes dates along the year based on a schedule e.g. daily, weekly, monthly etc. I am looking to Mark an "X" and also do conditional formatting for each cell marked "x" The Columns are
A - Machine Name
B - Work Instruction
C- Scheduled Frequency
D - Last Maintenance Date
E moving forward - days of the year within which maintenance is required to be done.

Machine Name​	Work instruction​	Schedule Frequency​	Last Maintenance Date​	1/10/2021	2/10/2021	3/10/2021	4/10/2021
Cutter	replace the blades	weekly	13/10/2021
	Replace the Cam wheels	Monthly	29/9/2021
	Check the Vacuum Tubes	Daily	13/10/2021
Opener	Check The Springs	Daily	13/10/2021
	Check the Arm Bar	weekly	10/10/2021
	Replace the main Bearing	Annually	6/6/2021
	Grease the lower cam shaft	6 Months	6/6/2021

. An example of such a table is shown below

 

[XLfriend777]

Hello Daniel Kimathi and welcome to the forum. You might try something like the code below which places an x every 7 days (weekly) and then modify it for your requirements. Hope this helps get you started.

Sub Weekly()
i = 5
    While i < 366 'Runs for about one year
        Cells(2, i).Value = "X"
        i = i + 7
    Wend
End Sub