Excel - Force recalculation when different cell is selected

Question

A bit of context:

I recently discovered that the following formula returns the address of the cell that is currently selected (or if a range is selected, returns the address of the upper-left most cell in the range):

= CELL("address")

At first, I thought this formula would be useful for conditional formatting, since it could be used as part of a condition to only format the cell that is selected (e.g. the conditional formatting rule could be something like = CELL("address")=ADDRESS(ROW(),COLUMN())), but I am facing an obstacle.

The formula is volatile, but volatile functions only update either when:

A cell in the worksheet is changed
F9 is pressed on the keyboard

All that said, my question is: Is there a way to have a cell automatically recalculate whenever a different cell is selected with a mouse click? Even volatile cells won't update from this condition, because selecting a different cell, in itself, won't cause any data in the cells to change.

Of course, it could be updated manually by pressing F9 after selecting a different cell, but I am wondering if there is a way to automate this.

Create a Worksheet_SelectionChange event that forces the recalc. — Scott Craner, Feb 08 '18 at 19:44
Doyou want to re-calculate the entire worksheet or only a specific cell? — Gary's Student, Feb 08 '18 at 19:44
@Gary'sStudent Preferably, only a specific cell. Ideally, something as simple as `= FORCE_RECALC(CELL("address"))`, but I doubt it would be that simple. — ImaginaryHuman072889, Feb 08 '18 at 19:45

score 2 · Accepted Answer · answered Feb 08 '18 at 19:57

2

You can use the Worksheet_SelectionChange() event in VBA.

Open your VBE (Alt+F11), find your workbook in the VBAProject pane (upper left) and double click your worksheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'Force this cell to recalculate any time any cell is selected/highlighted
    Range("A1").Calculate
End Sub

Now anytime moves around on the worksheet Cell A1 will recalculate.

answered Feb 08 '18 at 19:57

JNevill

46,980
4
38
63

Just did this. This seems to update the entire sheet, not just cell `A1`. Forgive me if I'm doing something wrong, not very familiar with VBA. – ImaginaryHuman072889 Feb 08 '18 at 20:03
1

As far as I know, this is as good as it gets. It could be that the change in A1 triggers the rest of the sheet to calculate... ? – JNevill Feb 08 '18 at 20:06
That's unfortunate. This makes the entire sheet super volatile because every cell will recalculate any time a cell changes or anytime a different cell is selected. If I don't get any more answers soon, I'll accept this. Thanks for your help. – ImaginaryHuman072889 Feb 08 '18 at 20:11
@ImaginaryHuman072889 The posted code could be modified to re-calculate only if a specific cell is *Selected* rather than any cell. – Gary's Student Feb 08 '18 at 20:16
@Gary'sStudent Ah, ok. Yes, I want cell `A1` **only** to recalculate if *any* cell is *selected*. Not sure if this is possible. – ImaginaryHuman072889 Feb 08 '18 at 20:18
That's what this code is doing. If **any** cell is selected, then this cell recalculates (causing, apparently, every other cell to recalculate). What Gary's Student was suggesting is that if there is only a small range you wish to detect a selection change in (Say A1:F50) then, and only then, recalculate A1. – JNevill Feb 08 '18 at 20:21
@ImaginaryHuman072889 the problem is too much volatility...another option is don't put a formula in **A1**. Let the VBA calculate whatever you want in **A1** and then have the VBA put the result in **A1** – Gary's Student Feb 08 '18 at 20:22
@Gary'sStudent I was thinking that as an option too, but it sounds like the formula being used (if I'm reading the question right) would be difficult to replicate in VBA. I may be very wrong about that though since I wasn't too sure what it was doing. – JNevill Feb 08 '18 at 20:23
1

@ImaginaryHuman072889 all volatile function, which `Cells()` is one, will recalc every time any cell,sheet, or workbook recalcs. There is no stopping that, without writting your own version of Cells() that is not volatile. – Scott Craner Feb 08 '18 at 20:23

Scott Craner · Answer 2 · 2018-02-08T20:33:04.257

1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Range("A1:D4"), Target) Is Nothing Then
    Range("A1:D4").Interior.Color = xlNone
    Target.Interior.ColorIndex = 6
End If
End Sub

This will now highlight the cell chosen only if the cell chosen is in A1:D4

edited Feb 08 '18 at 20:33

answered Feb 08 '18 at 20:27

Scott Craner

148,073
10
49
81

Excel - Force recalculation when different cell is selected

2 Answers2