Worksheet Change: If value of cell in certain range >c8 -> MsgBox and if value in other cell (that has function in it) > 300 -> MsgBox

Question

I want a worksheet change macro, that pops up a Messagebox whenever a value higher than 8 is put in one of the cells in range (F14:J26) and if a value greater than 300 is put in cell C37.

My problem is that cell C37 is not filled out manually but has a formula in it so it is a calculation of two other cells. And I think excel doesn't recognize this as a value and therefore doesn't do anything whenever the result in that cell is higher than 300.

Option Explicit

 Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Range("F14:J26")) Is Nothing Then
            If Target.Value > 8 Then
                MsgBox "Was that accepted?"

          End If
    End If

   If Not Application.Intersect(Target, Range("C37")) Is Nothing Then
            If Target.Value > 300 Then
                 MsgBox "Was that accepted?"

          End If
    End If

End Sub

The first part of the code works as it should. But the second part as explained above doesn't. I also tried to split it in two separate codes but that shows a bug. Any help on this would be very much appreciated!

Use worksheet.calculate event, instead of change when using values from formula's. https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.calculate(even) — Luuklag, Feb 11 '19 at 08:26
Note that if `Target` is more than one cell then `Target.Value` will return an array of values and `Target.Value > 300` fails. — Pᴇʜ, Feb 11 '19 at 08:27
Possible duplicate of [VBA code doesn't run when cell is changed by a formula](https://stackoverflow.com/questions/11406628/vba-code-doesnt-run-when-cell-is-changed-by-a-formula) — FunThomas, Feb 11 '19 at 08:36

score 2 · Answer 1 · answered Feb 11 '19 at 08:54

2

You have two (maybe more) possible options:

Use the "Worksheet_Calculate"-event, instead of the "Worksheet_Change"-event.
If the formular in C37 is fixed, don't monitor C37 but the source-cells instead.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Range("F14:J26")) Is Nothing Then
          If Target.Value > 8 OR Range("C37").Value > 300 Then
                MsgBox "Was that accepted?"

          End If
    End If
End Sub

answered Feb 11 '19 at 08:54

Gerrit

170
2
11

1

This will test `Range("C37").Value > 300` **only** if `Target` is intersecting `Range("F14:J26")` but not if `Range("C37").Value` changes because of something else. So this will work while the formula in C37 **exclusively** depends on values in `F14:J26` and nothing else. – Pᴇʜ Feb 11 '19 at 09:26
2

Also note that this will still fail if `Target` is a range (multiple cells) and not a single cell (this happens eg when you copy/paste multiple cells). `Target.Value` will then result in an array of values that cannot be compared `>8`. Therefore you would need to loop through `Application.Intersect(Target, Range("F14:J26")).Cells` and test each cell for `>8`. – Pᴇʜ Feb 11 '19 at 09:26

Pᴇʜ · Accepted Answer · 2019-02-12T07:15:11.907

In addition to Gerrit's anwser I recommend to extend it in the following way, so it doesn't fail if someone pastes a data range into F14:J26.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim AffectedRng As Range
    Set AffectedRng = Application.Intersect(Target, Range("F14:J26"))

    Dim FoundInvalidData As Boolean

    If Target.Parent.Range("C37").Value > 300 Then 
        FoundInvalidData = True
    ElseIf Not AffectedRng Is Nothing Then
        Dim Cel As Range
        For Each Cel In AffectedRng.Cells
            If Cel.Value > 8 Then
                FoundInvalidData = True
                Exit For
            End If
        Next Cel
    End If

    If FoundInvalidData Then
        MsgBox "Was that accepted?"
    End If
End Sub

Worksheet Change: If value of cell in certain range >c8 -> MsgBox and if value in other cell (that has function in it) > 300 -> MsgBox

2 Answers2