Excel Force Calculation

Question

I have a cell that is evaluated by

=IF(OR(J41="",J40=""),"",(1-($J$41/$J$40)))

computing the percent error between two cells that the user inputs. Additionally, I have an IFC on a seperate sheet that is validating this cell, among other cells, and setting it to a certain color with a warning if the percent error value is above/below a certain number. The problem is that the cell does not show the warning unless I click on it and hit enter (or F2 + Enter), which calculates the cell and populates the value at that time. Is there a way to Force the calculation to occur in that cell when the user inputs values into J41 and J40, thus populating the warning immediately? I have checked multiple threads on this, some say use the Application.Volatile statement, but I am not too sure if that will work..

Any suggestions?

Answer 1

Manually calculating a range is as simple as using Range.Calculate. To run code when a value is changed in a certain range, use Worksheet_Change.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngTrigger As Range: Set rngTrigger = Range("D5")
    Dim rngTarget As Range: Set rngTarget = Range("E5")

    If Not Intersect(Target, rngTrigger) Is Nothing Then _
        rngTarget.Calculate
End Sub