auto border every 4 week on the right

[glda19]

hi
I have made a calandar and every 4 weeks i need a red tick line on the right of the cels.

So for 2017 the first line is on the weeknumber 3 than 7 than 11 and the last line for 2017 is in week 51
But when i change the year the lines must auto change
so for 2018 it must be week 3 7 11 15 and so on

[dflak]

I had to do some checking since I noticed that some week numbers were repeated.

Other than that I recorded a macro so I could get the syntax for a red border, and then used the MOD operator to determine if it was a 4th week.

[glda19]

but how did you actived the macro

[dflak]

Go to the View ribbon and select Macro and you can select it from there. You can also use Options to assign a shortcut key such as CTRL-w or CTRL-q (those two CTRL keys are used to start and stop scrolling so remapping them won't do much damage).

[glda19]

but i your example file when i pres on the up down button it change the lines So how do you make that the macro is started

[dflak]

When you get to view the macro, you can either run it or if you click on Options, you can assign it to a CTRL key.

[glda19]

i think you don't under stand me
When i download your example and when i pres the up down buttun the macro automaticly runs . How did you do that

[dflak]

It sounds like we are literally not on the same page. What up and down button are you talking about?

[glda19]

tel me how you start your macro in your example.
The up down button is on the sheet kalender on the top left

[dflak]

I hadn't noticed that thing before. It's called a spinner and it can change the years between 1990 and 2200 in increments of 1.

It does not run the macro for me. I think what you are seeing is a mistake on my part - there is a weakness in the macro I created. It does not clear the formatting when the year is changed. That might be OK for a couple of years since the weeks within the years might not change. Eventually, you may come across a year where the weeks don't line up the way they do now. Then you may get "double" red lines.

Here's what I can do: I can write a routine to restore the default formatting and run it as the first step in the macro that does the lines. Then I can use a change event so when you change the year, it will fire the macro automatically.

However, we have another problem. The reason we are putting a red line behind week 3 of 2017 is that Sunday is actually week 52 of the previous year. If Monday is the first of the year, then we would have to put the first red line behind week 4. I can adjust the code to look at cell B3. If it's 52, I will do one thing. If it's 1, I will do another.

At the moment, I am running the macro using View -> Macro -> Run. As I mentioned above, in the next version, I will tie it to the cell affected by the spinner.

I have this all mapped out in my mind, but it will take me a day or two to program it.

[dflak]

I can probably program this, but I can't test it. What happens when I change the year from 2017 to anything else, I get #VALUE errors all over the place. When I change it back, the errors don't go away. There is some sort of defect in the logic that is generating the dates.

If this is your program, can you fix this error?