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.
You can however use the DisplayFormat property to access the conditional formatting format.
Note that the DisplayFormat property will not work in a User Defined Function.

Sub WhichTypeOfFormatting() 
Dim sCellAddress As String 
    sCellAddress = "B1" 
    If (Range(sCellAddress).Interior.ColorIndex <> XlColorIndex.xlColorIndexNone) Then 
        Debug.Print sCellAddress & " has cell formatting" 
    Else 
        If (Range(sCellAddress).DisplayFormat.Interior.ColorIndex <> XlColorIndex.xlColorIndexNone) Then 
            Debug.Print sCellAddress & " has conditional formatting" 
        Else 
            Debug.Print sCellAddress & " has no formatting" 
        End If 
    End If 
End Sub 

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("A1:A10").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("A1:A10").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(ByVal 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(ByVal 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(ByVal rgeRange As Range, _ 
                            ByVal 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