I have a strange issue trying to set the colors of a conditional format with VBA.
Create a new workbook and set the conditional format how you'd like. In my case I picked the middle grey color. Actually two conditions but... I then run the following.
Sub GetConditionalFontColor()
colorVal = ActiveCell.DisplayFormat.Font.Color
MsgBox "Font Color is " & colorVal
colorVal = ActiveCell.DisplayFormat.Font.TintAndShade
MsgBox "Font TintAndShade is " & colorVal
colorVal = ActiveCell.DisplayFormat.Interior.Color
MsgBox "Interior Color is " & colorVal
colorVal = ActiveCell.DisplayFormat.Interior.TintAndShade
MsgBox "Interior TintAndShade is " & colorVal
End Sub
Ok, more than needed but copy\paste. Anyway the color value that it will return is 12566463
Now I use the following code to set the value, again copy\paste so more info than the "simple" question as well as other code redacted.
Sub ResetCondFormatting()
ActiveCell.Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = 12566463
.TintAndShade = -0.1499679556505
End With
With Selection.FormatConditions(1).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 12566463
.TintAndShade = -0.1499679556505
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=1.25"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = 255
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub
If I run the GetConditionalFontColor after using the ResetCondFormatting it then returns a value of 10724259 for some reason which appears to be the color set as you can see visually if you format two cells and compare.
Any ideas as to what's going on?
Thanks in advance,
To lighten and darken colors in VBA, you can use the .TintAndShade
property. It can be given values in the range of -1..+1
, with -1
being completely darkened (black), +1
being completely brightened (white), 0
being a neutral value (not changing color). In Excel, you can use ready-made sets of colors, the so-called accent colors, associated with the colors of the selected theme. These colors have fixed TintAndShade
values.
TintAndShade
property is tightly bound to the Color
property. Any direct value assignment to the Color
resets TintAndShade
, while any value assignment to TintAndShade
modifies (implicitly and according to an unpublished algorithm) the Color
property. Only the value of this modified color can be read. When reading Color
has a current value that differs from the original value if .TintAndShade <> 0
.
TintAndShade
property works by storing the original color value. When setting the TintAndShade
one always refers to the original color. Thanks to this, after assigning 0
to this property, we can accurately reproduce the original color. If this color was not stored, it would not be possible to reproduce it accurately, because when the color is lightened or dimmed, there is a loss of accuracy, because each RGB component of the color is stored in 1 byte.
Unfortunately, when the value of .TintAndShade <> 0
one cannot directly read the original color, only the current one. To reach the original color you need to set .TintAndShade = 0
and then do a read. TintAndShade
value can be stored in a variable and reused later.
To read the original color into the clr
variable, use the sequence:
Dim ts As Double, clr As Long
ts = .TintAndShade
.TintAndShade = 0
clr = .Color
.TintAndShade = ts
When setting a color in VBA, there is no need to reset the .TintAndShade
property separately, because each setting of the .Color
automatically resets .TintAndShade
.