VBA Code


Color and ColorIndex

The Color and ColorIndex properties of a Range object do not return the colour of the cell if the formatting has been applied as a consequence of Conditional Formatting.
The only way around this is to actually text the individual Conditions.



Using the Range.FormatConditions Collection

This collection contains all the conditional formats for a single range.
The FormatConditions collection can contain up to three conditional formats.
Each format is represented by a FormatCondition object.
Use the FormatConditions property to return a FormatConditions object.
Use the Add method to create a new conditional format, and use the Modify method to change an existing conditional format.

With Worksheets(1).Range("e1:e10").FormatConditions _ 
        .Add(xlCellValue, xlGreater, "=$a$1")
    With .Borders
        .LineStyle = xlLineStyle.xlContinuous
        .Weight = xlBorderWeight.xlThin
        .ColorIndex = 6
    End With
    With .Font
        .Bold = True
        .ColorIndex = 3
    End With
End With

If you try to create more than three conditional formats for a single range, the Add method fails.
If a range has three formats, you can use the Modify method to change one of the formats, or you can use the Delete method to delete a format and then use the Add method to create a new format.


FormatCondition Object

Represents a conditional format. The FormatCondition object is a member of the FormatConditions collection.
Use FormatConditions(index), where index is the index number of the conditional format, to return a FormatCondition object.

With Worksheets(1).Range("e1:e10").FormatConditions(1) 
    With .Borders
        .LineStyle = xlLineStyle.xlContinuous
        .Weight = xlBorderWeight.xlThin
        .ColorIndex = 6
    End With
    With .Font
        .Bold = True
        .ColorIndex = 3
    End With
End With

Use the Font, Border, and Interior properties of the FormatCondition object to control the appearance of formatted cells. Some properties of these objects aren't supported by the conditional format object model. The properties that can be used with conditional formatting are listed in the following table.



Font Object


Bold
Color
ColorIndex
FontStyle
Italic
Strikethrough
Underline
The accounting underline styles cannot be used.


Border Object


Bottom
Color
Left
Right
Style
The following border styles can be used (all others aren't supported): xlNone, xlSolid, xlDash, xlDot, xlDashDot, xlDashDotDot, xlGray50, xlGray75, and xlGray25.
Top
Weight
The following border weights can be used (all others aren't supported): xlWeightHairline and xlWeightThin.



Interior Object


Color
ColorIndex
Pattern
PatternColorIndex


Formula1 Property

Copying and pasting a cell over a cell that uses conditional formatting wipes out the formatting rules
There is no direct way for your VBA code to determine if a particular cell's conditional formatting has been "triggered."
When a Conditional Formatting formula uses a relative range reference, accessing the Formula1 using VBA gives you a different formula, depending on the active cell position.
Convert the formula to R1C1 notation using the active cells as the reference
Then convert that R1C1 formula back to A1 style

F1 = Range("A1").FormatConditions(1).Formula1 
F2 = Application.ConvertFormula(F1, xlA1, xlR1C1, , ActiveCell)
F1 = Application.ConvertFormula(F2, xlR1C1, xlA1, , Range("A1"))

Highlighting the first "different" value in a column

Public Function FirstDifferentValue(rgeRange As Range) As Boolean 
Static bpreviousshaded As Boolean
   If rgeRange.Row = 1 Then
      bpreviousshaded = True
      FirstDifferentValue = True
      Exit Function
   End If
   If rgeRange.Value = rgeRange.Offset(-1, 0).Value Then
      bpreviousshaded = True
      FirstDifferentValue = True
   Else
      FirstDifferentValue = Not bpreviousshaded
      bpreviousshaded = False
   End If
End Function

Highlighting alternate values in a column

Public Function DifferentDate(rgeRange As Range) As Boolean 
Static bpreviousshaded As Boolean
   If rgeRange.Row = 1 Then
      bpreviousshaded = False
      DifferentDate = False
      Exit Function
   End If
   If rgeRange.Value = rgeRange.Offset(-1, 0).Value Then
      DifferentDate = bpreviousshaded
   Else
      If bpreviousshaded = False Then
         DifferentDate = True
         bpreviousshaded = True
      Else
         DifferentDate = False
         bpreviousshaded = False
      End If
   End If
End Function

Highlighting the other columns in a table

Public Function ShadeColumn(rgeRange As Range, _ 
                            iValueCol As Integer) As Boolean
Static bpreviousshaded(10) As Boolean
   If rgeRange.Row = 1 Then
      bpreviousshaded(rgeRange.Column) = False
      ShadeColumn = False
      Exit Function
   End If
   If rgeRange.Offset(0, iValueCol - rgeRange.Column).Value = _
                             rgeRange.Offset(-1, iValueCol - rgeRange.Column).Value Then

      ShadeColumn = bpreviousshaded(rgeRange.Column)
   Else
      If bpreviousshaded(rgeRange.Column) = False Then
         ShadeColumn = True
         bpreviousshaded(rgeRange.Column) = True
      Else
         ShadeColumn = False
         bpreviousshaded(rgeRange.Column) = False
      End If
   End If
End Function


© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext