excelvba

Excel VBA Conditional Formatting values


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,


Solution

  • Brighten and Darken Colors in Excel VBA

    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.