Code to determine if a cell has increased, to equal another cell?

hazmat

New Member
Joined
Jun 14, 2019
Messages
19
I have code that pops up a message box when cell F1 equals cell F5 working fine, but i would like the message box to only pop up if F1 has increased in value to equal F5.
F5 = 33.
F1 =COUNT(E8:E37,I8:I37,L8:L37,O8:O37,R8:R37)

So, if F1 changes from 32 to 33, the message pops up, but if F1 changes from 34 to 33, nothing happens.
Is this possible?

Thanks

This is the beginning of the sub routine that i currently have:

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Range("F1").Value = Range("F5").Value Then
    
     Result = MsgBox("END OF ROUND" & vbNewLine & "Click 'OK' to start new round and save this round" & vbNewLine & "Click 'Cancel' ONLY to make changes to current round", vbOKCancel + vbCritical, "End of Round")
        If Result = vbOK Then
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

mabbutt

Board Regular
Joined
Oct 4, 2014
Messages
102
This may not be the best solution as I am not an advanced VBA user but it is one that works for the problem you have.

Place this VBA code in the sheet that you have the data in F1 and F5:

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    
    Application.Run "Module1.Increased_Check"

End Sub

Place this VBA code in the Workbook:

VBA Code:
Private Sub Workbook_Open()

    Application.Run "Module1.SetF1Value"

End Sub

Place this code in a new module (you will have to amend the code to match this name e.g. 1,2,3 etc):

VBA Code:
Dim TempF1Value As Integer

Private Sub SetF1Value()

    TempF1Value = 0
    TempF1Value = Sheets("Sheet1").Range("F1").Value

End Sub

Private Sub Increased_Check()

    If TempF1Value < Sheets("Sheet1").Range("F5").Value Then
    
        If Sheets("Sheet1").Range("F1").Value = Sheets("Sheet1").Range("F5").Value Then
    
            MsgBox Prompt:="F1 has increased in value to match F5"
    
        End If
        
    End If
    
    SetF1Value

End Sub

Please note that I have used Application.Run because I have used Private subs but this is not necessary it is just the way I have done it.

You will need to amend the sheets names to the ones you have used.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,619
Messages
5,659,903
Members
418,536
Latest member
Tezzies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top