Hej,
So I am trying to use VBA to add conditonal formatting to compensate for some other coding, which changes the ranges from time to time. My problem is, that I only the conditional formatting to apply to xlEdgeRight
and xlEdgeLeft
. However, VBA always tells me that it cannot set the border style. Any ideas?
Dim rngMark As Range
Dim DateCond As FormatCondition
Dim BordNum As Long
Call wsDef
Set rngMark = wksS.Range("E11:CPB25")
rngMark.FormatConditions.Delete
Set DateCond = rngMark.FormatConditions.Add(Type:=xlExpression, Formula1:="=AND(TODAY()>=E$7,TODAY()<F$7)")
DateCond.SetFirstPriority
With DateCond
.StopIfTrue = False
.Font.ThemeColor = xlThemeColorAccent2
.Font.Bold = True
.Borders.LineStyle = xlNone
.Borders.LineStyle = xlNone
End With
For BordNum = 7 To 8
With DateCond.Borders(BordNum)
.LineStyle = xlContinuous
.TintAndShade = 0
.Color = -16776961
.Weight = xlThin
End With
Next BordNum
CodePudding user response:
Programming formatconditions - especially borders - is a bit special.
You have to use xlLeft
= -4131 and xlRight
= -4152 in this case:
Dim BordNum As Long
Dim arrBordNum(1) As Long
arrBordNum(0) = -4131 'xlleft
arrBordNum(1) = -4152 'xlright
For BordNum = 0 To UBound(arrBordNum)
With DateCond.Borders(arrBordNum(BordNum))
.LineStyle = xlContinuous
.TintAndShade = 0
.Color = -16776961
.Weight = xlThin
End With
Next BordNum