CompareColumns Object - Microsoft Office Excel

Version Information
Version Added: Excel

Remarks

Using the CompareColumns object, you can easily compare and contrast values in table columns. For example, you can format values with a light blue cell color in an Actuals column if they are below the values in a Target column in the same row. Or, you can format the entire row a green or red font color, depending on whether an Income column is greater or less than an Expense column.

This object can only be applied to a range of data that is entirely in a single table. In the object model, a table is represented as a ListObject.

Example

The following example adds sample data to a worksheet and then applies a table to the data by creating a new ListObject. It then applies a conditional formatting rule which modifies the text of a row to red if the values in the "Actual" column is less than the values in the "Target" column. This sample code must be run in a new workbook.

Visual Basic for Applications

Visual Basic for Applications
Sub CreateCompareColumnsCF() ' Create object variables for the condition format and the table Dim cfCompareCol As CompareColumns Dim objStatusTable As ListObject ' Add sample data With ActiveSheet .Range("C2") = "Name" .Range("D2") = "Target" .Range("E2") = "Actual" .Range("C3") = "Erich" .Range("D3") = "100" .Range("E3") = "145" .Range("C4") = "John" .Range("D4") = "125" .Range("E4") = "88" .Range("C5") = "Mike" .Range("D5") = "95" .Range("E5") = "70" .Range("C6") = "Angela" .Range("D6") = "130" .Range("E6") = "170" .Range("C7") = "Jeana" .Range("D7") = "100" .Range("E7") = "120" .Range("C8") = "Mark" .Range("D8") = "75" .Range("E8") = "73" .Range("C9") = "Tony" .Range("D9") = "85" .Range("E9") = "89" End With Range("C2:E9").Select ' Create a table (list) object from the sample data Set objStatusTable = ActiveSheet.ListObjects.Add(xlSrcRange, _ Range("C2:E9"),, xlYes) objStatusTable.Name = "StatusTable" Range("StatusTable[Actual]").Select ' Add a conditional format to the Actual column Set cfCompareCol = Selection.FormatConditions.AddCompareColumns ' Set which column to compare to and specify that the condition will evaluate ' to TRUE if the values in the Actual column is less than the Target values cfCompareCol.Operator = xlLess cfCompareCol.Column1 = "Target" ' Change the font to red and apply to the entire row if TRUE With cfCompareCol.Font .Color = -16383844 .TintAndShade = 0 End With cfCompareCol.FormatRow = True End Sub

Sub CreateCompareColumnsCF() ' Create object variables for the condition format and the table Dim cfCompareCol As CompareColumns Dim objStatusTable As ListObject ' Add sample data With ActiveSheet .Range("C2") = "Name" .Range("D2") = "Target" .Range("E2") = "Actual" .Range("C3") = "Erich" .Range("D3") = "100" .Range("E3") = "145" .Range("C4") = "John" .Range("D4") = "125" .Range("E4") = "88" .Range("C5") = "Mike" .Range("D5") = "95" .Range("E5") = "70" .Range("C6") = "Angela" .Range("D6") = "130" .Range("E6") = "170" .Range("C7") = "Jeana" .Range("D7") = "100" .Range("E7") = "120" .Range("C8") = "Mark" .Range("D8") = "75" .Range("E8") = "73" .Range("C9") = "Tony" .Range("D9") = "85" .Range("E9") = "89" End With Range("C2:E9").Select ' Create a table (list) object from the sample data Set objStatusTable = ActiveSheet.ListObjects.Add(xlSrcRange, _ Range("C2:E9"),, xlYes) objStatusTable.Name = "StatusTable" Range("StatusTable[Actual]").Select ' Add a conditional format to the Actual column Set cfCompareCol = Selection.FormatConditions.AddCompareColumns ' Set which column to compare to and specify that the condition will evaluate ' to TRUE if the values in the Actual column is less than the Target values cfCompareCol.Operator = xlLess cfCompareCol.Column1 = "Target" ' Change the font to red and apply to the entire row if TRUE With cfCompareCol.Font .Color = -16383844 .TintAndShade = 0 End With cfCompareCol.FormatRow = True End Sub

See also: