Compare 2 ranges in Excel. SUBTOTAL Function. VBA Codes

This month we will look at how to easily compare 2 ranges and color code the differences. The method we use is going to make use of Conditional Formatting, found under "Format" on the Worksheet Menu Bar. I have prepared a Workbook with the ranges already compared and it can be downloaded here .

For the purpose of the exercise, the ranges we will be comparing are A1:C6 and A10:C15 with row 1 of both ranges being headings and so not included in the comparison.

1) Select the range A11:C15, Starting from A11 and then go to Format>Conditional Formatting

You should now see all cells in the range A11:C15, that are different to the corresponding cells in the range A2:C6, formatted as chosen.

Sometimes you may prefer to not highlight on a cell-by-cell basis, but a row-by-row basis. That is, if only one cell in the row is different to the corresponding cell in the corresponding row, highlight the row instead of only the cell. This again can be seen in the Workbook that can be downloaded here .

1) Select the range A11:C15, Starting from A11 and then go to Format>Conditional Formatting

You should now see all rows in the range A11:C15, that are different to the corresponding rows in the range A2:C6, formatted as chosen. It is vital to note the use of Absolute Column references in the NOT function.

Let's now take a look at how we can use a single SUBTOTAL function to do all of the functions below;

The SUBTOTAL Function in Excel is used to perform a specified function on a range of cells that have had AutoFilters applied to them. When the Auto Filter has been applied the SUBTOTAL function will only use the visible cells, all hidden rows are ignored. The operation it performs is solely dependent on the number (between 1 and 11) that we supply to its first argument Function_num

If you are not already aware, Excels SUBTOTAL function, can perform these formulas by allowing us to supply a number (between 1 and 11) to the Function_num argument. Where 1 would be AVERAGE and 11 would be VARP.

First thing you need to do is place the list of 11 functions above into continuous cells in a single column. After doing this, select the 11 cells and click in the "Name box" (left of the formula bar) and type the word "Functions", without the quotations, then push Enter.

Now select any blank cell and go to Data>Validation. Select "List" from the "Allow" box and in the "Source" box type: =Functions then click OK. Now name this cell "Formula" in the same way as described above.

Now select the cell that you wish to house the SUBTOTAL function and Enter this formula;

Where $A$1:$A$200 is the range you wish to use the SUBTOTAL function on. You will of course get #N/A when the named cell Formula is blank so you need to select this cell and choose your desired formula from the list.

Excel VBA Tips and Tricks

Excel Analysis Collection 40+ Excel Add-ins & 3 downloadable books on Excel VBA

This month I'm going to include what appears to be the most popular selection of VBA codes from our site, please enjoy and don't forget to use our free question and answer forum for help.

It is not too uncommon for an Excel Workbook to be used to collect data for such things as surveys etc. Perhaps the most common way this is done, is to offer the user x amount of answer choices and place a Checkbox along-side each choice. The user then checks the appropriate checkboxes. The problem with using this method is that your Workbook can soon end up with hundreds of checkboxes. This can not only make a Workbook unstable but can also greatly increase file size!

Let's look at just how easily we can use some very simple VBA code to have any cell, within a specified range, ticked upon selecting it. If the cell within the specified range, already has a tick, the code will remove it. The trick to the code is the use of the letter "a" in a cell that has had it's font formatted to marlett. When the time comes to add up the results, it is simply a matter of using the COUNTIF Function to count the occurrences of the letter "a". For example

=COUNITIF($A$1:A$100,"a")

'CODE 1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
        Target.Font.Name = "Marlett"
        If Target = vbNullString Then
           Target = "a"
        Else
           Target = vbNullString
        End If
    End If
End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
     Cancel = True 'Prevent going into Edit Mode
        Target.Font.Name = "Marlett"
        If Target = vbNullString Then
           Target = "a"
        Else
           Target = vbNullString
        End If
    End If
End Sub

Once the desired code is in place, simply click the top right X to get back to Excel, save and you are ready to go.

Extract Address from a Hyperlink

This UDF will extract the underlying address from a cell containing a Hyperlink.Function GetAddress(HyperlinkCell As Range)
GetAddress = Replace _
(HyperlinkCell.Hyperlinks(1).Address, "mailto:", "")
End Function

To use this UDF push Alt+F11 and go Insert>Module and paste in the code. Push Alt+Q and save. The Function will appear under "User Defined" in the Paste Function dialog box (Shift+F3). Use the Function in any cell as shown below.

=GetAddress(A1)
Where cell A1 has a Hyperlink within it.

Excel has a very useful feature in Excel named Conditional Formatting. It can be found via Format>Conditional Formatting on the Worksheet Menu Bar. The feature allows us to format a cell based on its content. For example, if the cell is greater than 5, but less then 10, we may have the cell change to a red background. We can do this for up to 3 conditions only, which sometimes is not enough. If we want more than 3 conditions we can use Excel VBA code that is automatically fired whenever a user makes any change in a specified range. Let's assume we want to have 6 separate conditions in the range A1:A10 on a particular Worksheet. To do so try this. Activate the Worksheet then right click on its name tab and select "View Code". In here put the code as shown below;

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer
If Not Intersect(Target, Range("A1:A10")) is Nothing Then
Select Case Target
Case 1 To 5
     icolor = 6
Case 6 To 10
     icolor = 12
Case 11 To 15
     icolor = 7
Case 16 To 20
     icolor = 53
Case 21 To 25
     icolor = 15
Case 26 To 30
     icolor = 42
Case Else
   'Whatever
End Select
Target.Interior.ColorIndex = icolor
End If

End Sub

Now click the top right X, or use Alt+Q, to get back to your Worksheet. Enter some numbers in range A1:A10 between 1 to 3 and the background color of each cell will change. The color used is determined by the passing of a number to the variable icolor, which in turn passes this number to Target.Interior.ColorIndex The number that is passed is determined by the Case x To x For example, if we enter the number 22 in any cell within the range A1:A10 the number 15 is passed to icolor and then icolor (now having value of 15) is passed to the Target.Interior.ColorIndex making the cell grey. Target is always the cell that has changed and thus fired the code.

The only problem this leaves us with is what colors are represented by what numbers. We can obtain our needed number by recording a macro changing a cells background color, or you can go here for a Custom Function that will return a cells color by its ColorIndex number or by name.

Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft.
Contact Us

OzGrid Excel and VBA Newsletter

Excel Tips and Tricks

Excel Analysis Collection 40+ Excel Add-ins & 3 downloadable books on Excel VBA

Excel VBA Tips and Tricks

Excel Analysis Collection 40+ Excel Add-ins & 3 downloadable books on Excel VBA