Sprietzsche
03-24-2016, 04:49 AM
First of all… yay, my first post :hi:
I’m making a calendar entirely in VBA and I’m using Excel 2010. The calendar is used by my coworkers to register their hollidays. I’m experiencing problems with conditional formatting. I want to color the weekends, but cant seem to get a formula working. I can’t use A1-notation because the number of coworkers can change each year.
Here is how january looks like...
January
Person 1
Person 2
Person 3
Friday January 1
absent
absent
Saturday January 2
Sunday January 3
Monday January 4
Saturday and sunday should be in grey in the whole row, stopping at the last person.
Layout for the complete calendar:
January
February
March
April
May
June
July
August
September
October
November
December
To color the weekends, i could use something like =weekday($E6;2)>5
But things get messed up when the number of coworkers changes. Eg: february for this year starts at p6, but next year, it might be o6, or maybe something else.
I tried using R1C1, variables, a cell with a reference (and then use Indirect), but whatever I try, it won’t work.
Any suggestions?
Here is part of the code… I have adapted it slightly so it works on its own. It assumes you have all days of January in cells E5:E36
Aantaldagen = number of days in a month (I typed 31 here, but this number changes depending on the month, and this value comes from another worksheet)
Personeelsteller = number op coworkers. Again: I just typed 9 here, but this normally gets its value from a formula in another worksheet)
a and b are used as a startingpoint for drawing the calendar.
Sub voorwop()
Dim a As Integer
Dim b As Integer
Dim aantaldagen As Integer
Dim personeelsteller As Integer
a = 5
b = 5
aantaldagen = 31
personeelsteller = 9
Range(Cells(a + 1, b), Cells(a + aantaldagen, b + personeelsteller)).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=WEEKDAG($E6;2)>5"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249946592608417
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub
In short: this works, but i want to change $E6 to something more dynamic
Thanks a lot in advance
I’m making a calendar entirely in VBA and I’m using Excel 2010. The calendar is used by my coworkers to register their hollidays. I’m experiencing problems with conditional formatting. I want to color the weekends, but cant seem to get a formula working. I can’t use A1-notation because the number of coworkers can change each year.
Here is how january looks like...
January
Person 1
Person 2
Person 3
Friday January 1
absent
absent
Saturday January 2
Sunday January 3
Monday January 4
Saturday and sunday should be in grey in the whole row, stopping at the last person.
Layout for the complete calendar:
January
February
March
April
May
June
July
August
September
October
November
December
To color the weekends, i could use something like =weekday($E6;2)>5
But things get messed up when the number of coworkers changes. Eg: february for this year starts at p6, but next year, it might be o6, or maybe something else.
I tried using R1C1, variables, a cell with a reference (and then use Indirect), but whatever I try, it won’t work.
Any suggestions?
Here is part of the code… I have adapted it slightly so it works on its own. It assumes you have all days of January in cells E5:E36
Aantaldagen = number of days in a month (I typed 31 here, but this number changes depending on the month, and this value comes from another worksheet)
Personeelsteller = number op coworkers. Again: I just typed 9 here, but this normally gets its value from a formula in another worksheet)
a and b are used as a startingpoint for drawing the calendar.
Sub voorwop()
Dim a As Integer
Dim b As Integer
Dim aantaldagen As Integer
Dim personeelsteller As Integer
a = 5
b = 5
aantaldagen = 31
personeelsteller = 9
Range(Cells(a + 1, b), Cells(a + aantaldagen, b + personeelsteller)).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=WEEKDAG($E6;2)>5"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriori ty
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249946592608417
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub
In short: this works, but i want to change $E6 to something more dynamic
Thanks a lot in advance