VBA To Populate Cells in a pattern

[mtagliaferri]

I have a shift planner which I populate days OFF with the letter 'O and it formats the cell colour to blue.
I would like to populate the row with a specific patter of 28 days which is 5 On, 5 Off, 4 On, 5 Off, 5 On, 4 Off, therefore when the patter is OFF I would like to populate the cell with the letter 'O'
more specifically I need this pattern to be able to start from the selected cell and populate the rows from there onwards and for the remaining sheets.
E.G in the sheet October if I start from the 3rd which is F7 I need to start the pattern from that cell till end of October (Cell AH7) and then continue with the pattern in November (D7 - AG7) and then December (D7- AH7). If I start from January then it will populate all sheets to December.

The difficult part is the leap year, the sheet populates the calendar section based on the year, therefore I have the additional column in February in order to accommodate the leap year.

Any thoughts if this is achievable? If so how?

 

[Micron]

I suppose you don't want to just enter O in a cell and have conditional formatting take care of the blue highlight?
I'm guessing that you don't want the pattern to be applied from the active cell on any sheet since Oct 1 and 2 don't fit the pattern. You must be wanting it to start from January 1?

I imagine it's possible with code; perhaps someone else knows a formula you can apply but that's not me. It might require that you
- select the cell for the first day in Jan where the pattern will begin
- do until sheet name = Dec And range = AF:? Not sure about 2nd condition
- offset 5
- loop 5, set cell value = O, colour cell blue
- offset 4
- loop 5, set cell value = O, colour cell blue
- offset 5
- loop 4, set cell value = O, colour cell blue
I gave some thought to using an array, collection or dictionary object instead but don't see the advantage to the extra work.

If this is a one-time issue and there are not a whole lot of rows, maybe you could format a template manually and use that as a starting point each year? I'm assuming you'd have a wb for each year.

 

[mtagliaferri]

I just need to enter 'O' in the Cell as the conditional formatting will take care of the rest.
I would also need to start this 5,5,4,5,5,4 pattern at anytome in the years hence selecting the cell where to start this pattern
Hope this makes sense

 

[Micron]

Then the above would be the same except no need to colour the cell. What's not clear is when it ends - last day in Dec or last day on the active sheet or something else.

 

[mtagliaferri]

Last day in December, and it would need to populate the last cell of February when leap year (maybe a condition to check if the cell contains '29' when a leap year is up. If not it keeps populating the pattern in March sheet and so on.
Also I am referring to the same row across all sheets.

 

[Micron]

Seems like you have all the details worked out. So your question has been answered?

Any thoughts if this is achievable? If so how?

 

[bebo021999]

By this:

I need this pattern to be able to start from the selected cell

How is cell selected? Click on the cell, or just use its address in code?
I suppose that you dont want to select cell to paste the pattern, and the pattern change when you click on other cell.

 

[mtagliaferri]

By this:

How is cell selected? Click on the cell, or just use its address in code?
I suppose that you dont want to select cell to paste the pattern, and the pattern change when you click on other cell.

Yes I would select the cell I want to start the pattern and apply to the row till the end of the sheet and then jump the the next sheet.
I have 4 rows I can potentially use for this pattern and would be row 4,5,6 and 7
With regards to the range, if row 7 is selected the range would be:
Jan D7:AH7
Feb D7:AE7 (If Leap year D7:AF7)
Mar D7:AH7
Apr D7:AG7
May D7:AH7
Jun D7:AG7
Jul D7:AH7
Aug D7:AH7
Sep D7:AG7
Oct D7:AH7
Nov D7:AG7
Dec D7:AH7
The pattern start on any cell selected onwards and would jump to the next sheet once reached the end of the range.

 

[bebo021999]

Hmm, Let say first select cell is D7
What happen if user select other cell, i.e, A1? or D8?
Will all cells with "O" and their format not change? or update?

 

[Micron]

Hmm, Let say first select cell is D7
What happen if user select other cell, i.e, A1? or D8?
Will all cells with "O" and their format not change? or update?

Good question. I thoughta button click event would be the way to go rather than cell selection. However, you'd need to first check if the active/selected cell is one of the corect ones.