Seeing the Difference on the Status Bar

@J.Wooley

Re "Sorry, but your method only works if the two cells are vertically adjacent."

It does work on two selected cells either in a row or a column. I agree it does not work on non-contiguous selected cells. Below is a modification that handles any two selected cells, contiguous or not.

In playing around with referencing cells within a selection, I noticed that how you select the cells makes a difference, even if the two cells are contiguous.

For example, if I click Cell A1 and drag to cell B1, the selection address is $A$1:$B$1. However if I click on cell B1 first and then select cell A1 by pressing the Ctrl key and clicking on A1, I get a different selection address: $B$1,$A$1.

This is important as the sign of the difference could be affected by how you select the two cells.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Displays the difference between two selected cells on the status bar.
' Modified 7/22/2020 to handle two non-contiguous cells

Dim c As Range
Dim sTemp As String
Dim Count As Integer
Dim Values(2) As Variant

If Target.Cells.Count = 2 Then
sTemp = ""

For Each c In Target
Count = Count + 1
If IsNumeric(c) Then
Values(Count) = c
Else
Values(Count) = 0
sTemp = " (non-numeric values in selected range"
sTemp = sTemp & "; result may be meaningless)"
End If
Next c
Application.DisplayStatusBar = True
Application.StatusBar = "Diff: " & Values(1) - Values(2) & sTemp
Else
Application.StatusBar = False
End If
End Sub

@Phil W
Sorry, but your method only works if the two cells are vertically adjacent. Here is another way to display a signed difference. This method applies to all sheets in the workbook, so the three event procedures must be located in the code window of the ThisWorkbook document module.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Selection.CountLarge = 2 Then
Call Workbook_SheetSelectionChange(Sh, Selection)
End If
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Application.StatusBar = False
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim v As Variant, c As Range
If Target.CountLarge = 2 Then
v = 0
For Each c In Target
If IsNumeric(c) Then
v = c - v
Else
v = -v
End If
Next c
Application.DisplayStatusBar = True
Application.StatusBar = "Difference: " & -v
Else
Application.StatusBar = False
End If
End Sub

(see Figure 1 below) is a screen shot of the code window.

If you want to display a signed difference between two values, and assuming you are always subtracting the second value in the selected range from the first one, here is a variation on the macro. This would more closely match the formula mentioned in Allen's comments.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Diff As Variant
Dim sTemp As String

If Target.Count = 2 Then
Diff = 0
sTemp = ""
If Not IsNumeric(Target(1)) Or Not IsNumeric(Target(2)) Then
sTemp = " (non-numeric values in selected range"
sTemp = sTemp & "; result may be meaningless)"
End If

If IsNumeric(Target(1)) Then ' Get first value in selected range
Diff = Target(1)
End If
If IsNumeric(Target(2)) Then ' Subtract the second value from the first value in selected range
Diff = Diff - Target(2)
End If

Application.DisplayStatusBar = True
Application.StatusBar = "Diff: " & Diff & sTemp
Else
Application.StatusBar = False
End If
End Sub