BET: Microsoft Excel Conditional Formatting - Sorting By Conditional Formatting

Range("C3").Interior.ColorIndex

Range("C3").Font.ColorIndex

Range("C3").Interior.ColorIndex
Range("C3").Font.ColorIndex

Dim objFormatConditions As FormatConditions

objFormatConditions(1).Interior.ColorIndex
objFormatConditions(1).Font.ColorIndex

9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

Option Explicit

Public Function ColourSorting(ByVal rgeCell As Range, _
                              ByVal bBackGround As Boolean, _
                              ByVal bText As Boolean) As Integer

Dim iconditionno As Integer

   If rgeCell.FormatConditions.Count = 0 Then Exit Function
   iconditionno = ConditionNo(rgeCell)
   If bBackGround = True Then
      ColourSorting = rgeCell.FormatConditions(iconditionno).Interior.ColorIndex
   End If
   If bText = True Then
      ColourSorting = rgeCell.FormatConditions(iconditionno).Font.ColorIndex
   End If
End Function

26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73

Private Function ConditionNo(ByVal rgeCell As Range) As Integer

Dim iconditionscount As Integer
Dim objFormatCondition As FormatCondition

    For iconditionscount = 1 To rgeCell.FormatConditions.Count
        Set objFormatCondition = rgeCell.FormatConditions(iconditionscount)
        Select Case objFormatCondition.Type
           Case xlCellValue
               Select Case objFormatCondition.Operator
                   Case xlBetween: If Compare(rgeCell.Value, ">=", objFormatCondition.Formula1) = True And _
                                           Compare(rgeCell.Value, "<=", objFormatCondition.Formula2) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlNotBetween: If Compare(rgeCell.Value, "<=", objFormatCondition.Formula1) = True And _
                                           Compare(rgeCell.Value, ">=", objFormatCondition.Formula2) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlGreater: If Compare(rgeCell.Value, ">", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlEqual: If Compare(rgeCell.Value, "=", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlGreaterEqual: If Compare(rgeCell.Value, ">=", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlLess: If Compare(rgeCell.Value, "<", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlLessEqual: If Compare(rgeCell.Value, "<=", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                   Case xlNotEqual: If Compare(rgeCell.Value, "<>", objFormatCondition.Formula1) = True Then _
                                           ConditionNo = iconditionscount

                  If ConditionNo > 0 Then Exit Function
              End Select

          Case xlExpression
            If Application.Evaluate(objFormatCondition.Formula1) Then
               ConditionNo = iconditionscount
               Exit Function
            End If
       End Select

    Next iconditionscount
End Function

74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92

Private Function Compare(ByVal vValue1 As Variant, _
                         ByVal sOperator As String, _
                         ByVal vValue2 As Variant) As Boolean

   If Left(CStr(vValue1), 1) = "=" Then vValue1 = Range(Mid(CStr(vValue1), 2)).Value
   If Left(CStr(vValue2), 1) = "=" Then vValue2 = Range(Mid(CStr(vValue2), 2)).Value

   If IsNumeric(vValue1) = True Then vValue1 = CDbl(vValue1)
   If IsNumeric(vValue2) = True Then vValue2 = CDbl(vValue2)

   Select Case sOperator
      Case "=": Compare = (vValue1 = vValue2)
      Case "<": Compare = (vValue1 < vValue2)
      Case "<=": Compare = (vValue1 <= vValue2)
      Case ">": Compare = (vValue1 > vValue2)
      Case ">=": Compare = (vValue1 >= vValue2)
      Case "<>": Compare = (vValue1 <> vValue2)
   End Select
End Function

	This user defined function will return the colour index of the cell reference which is passed as the first argument.
	The number returned will always be between 1 and 52 since this corresponds to the location on the colour palette.
	Once the user defined function has been entered into a code module in the corresponding VBA Project you can use it in your extra column.
	If the cell does not contain any conditional formatting then 0 is returned.

Leading the way in Microsoft Office Development

Using Manual Formatting

If you want to sort using the background colour of the cells you can refer to the Interior property of the Range object

If you want to sort using the colour of the text you can use the Font property of the Range object.

For more details please refer to the Sorting by Colour page.

Additional Column

Lets assume that you have a simple table of data and that the formatting has been applied using Conditional Formatting.

You must first add another column to your table of data.

This extra column will contain a user defined function which can be used to indicate the order in which to sort the rows.

Enter Conditions

Select the cells you want to apply the conditional formatting to, in this case "C3:C14".

Press (Format > Conditional Formatting) to display the Conditional Formatting dialg box.

Enter the three conditions, we have used constants to keep it simple.

Conditional Formatting

If a cell has been formatted with Conditional Formatting then using the ColorIndex of the cell will not work.

The following two properties are undefined when you have applied conditional formatting.

The only way to determine the type of formatting applied is to analyse the Conditional Formatting directly.

User Defined Function

This user defined function will return the colour index of the cell reference which is passed as the first argument.

The number returned will always be between 1 and 52 since this corresponds to the location on the colour palette.

Once the user defined function has been entered into a code module in the corresponding VBA Project you can use it in your extra column.

If the cell does not contain any conditional formatting then 0 is returned.