PDA

View Full Version : [SOLVED] Conditional formatting without using A1-notation in the rule



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

SamT
03-24-2016, 08:32 AM
Dim FormatParameter As String 'Rename FormatParameter To suit
FormatParameter = Cells(a + aantaldagen, b + personeelsteller).Address
Formula1:= "=WEEKDAG(" & FormatParameter & ";2)>5"

OR
Formula1:= "=WEEKDAG(" & Cells(a + aantaldagen, b + personeelsteller).Address & ";2)>5"


With Range(Cells(a + 1, b), Cells(a + aantaldagen, b + personeelsteller))
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=WEEKDAG(" & Cells(a + aantaldagen, b + personeelsteller).Address & ";2)>5"

.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1)
.StopIfTrue = False
With .Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249946592608417
End With
End With
End With

p45cal
03-24-2016, 09:45 AM
I can’t use A1-notation because the number of coworkers can change each year.The number of co-workers won't affect the conditional format formula, it will only affect which cells the conditional format is added to, and you handle that already.
The two commented-out suggestions in the code below will allow you to add conditional formatting wherever the calendar is on the sheet.
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
With Range(Cells(a + 1, b), Cells(a + aantaldagen, b + personeelsteller))
.FormatConditions.Add Type:=xlExpression, Formula1:="=WEEKDAG($E" & .Row & ";2)>5" 'this one assumes dates will always be in column E
'you could use one of the following 2 lines instead of the line above:
'.FormatConditions.Add Type:=xlExpression, Formula1:="=WEEKDAG(" & .Cells(1).Address(RowAbsolute:=False) & ";2)>5" 'this one assumes dates will always be in first column of your range.
'.FormatConditions.Add Type:=xlExpression, Formula1:=Application.ConvertFormula("=WEEKDAG(RC" & .Column & ";2)>5", xlR1C1, xlA1, , .Cells(1)) 'this one also assumes dates will always be in first column of your range.
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249946592608417
End With
.FormatConditions(1).StopIfTrue = False
End With
End Sub

Sprietzsche
03-25-2016, 03:56 AM
Thank you very muchos with nachos. It does what it is supposed to do :)

I'm still new in VBA and you gave me a few sollutions... As an excercise I'm going to try them all. For the moment, I will be using this:


With Range(Cells(a + 1, b), Cells(a + aantaldagen, b + personeelsteller))

.FormatConditions.Add Type:=xlExpression, Formula1:="=WEEKDAG(" & .Cells(1).Address(RowAbsolute:=False) & ";2)>5"
.FormatConditions(.FormatConditions.Count).SetFirstPriority
With .FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.249946592608417
End With
.FormatConditions(1).StopIfTrue = False
End With