Hello All! This is the first time I joined such a forum and I am really excited!
I am trying to improve a spreadsheet and I would like any assistance on the matter:
I have 2 sheets (sheet 1 and sheet 2). Sheet 2 is where the user inputs data, whereas Sheet 1 serves as the "totals sheet" where the totals are summed up. I am trying to write a code so that when the total in sheet 1 is greater than a value my spreadsheet would warn the user. Although I wrote a code for Sheet 1 which actually works...it only works if I manually add the value in the range in Sheet 1, however, when the value is input in Sheet 2 (which then is presented as a total in Sheet 1) the warning does not pop up...
Any help would be greatly appreciated
Thank you in advance!
Here is what I have so far:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("M3:M1827")) Is Nothing Then
If Range("M1829") >= 275000 Then MsgBox "Warning! You have reached or even exceeded the annual authorised limit for Legal Advice expenses.", vbCritical
If Range("M1829") >= 192500 And Range("M1829") < 247500 Then MsgBox "Warning! You have reached the 70% of the annual authorised limit for Legal Advice expenses.", vbInformation
If Range("M1829") >= 247500 And Range("M1829") < 275000 Then MsgBox "Warning! You have reached the 90% of the annual authorised limit for Legal Advice expenses.", vbExclamation
End If
End Sub
I am trying to improve a spreadsheet and I would like any assistance on the matter:
I have 2 sheets (sheet 1 and sheet 2). Sheet 2 is where the user inputs data, whereas Sheet 1 serves as the "totals sheet" where the totals are summed up. I am trying to write a code so that when the total in sheet 1 is greater than a value my spreadsheet would warn the user. Although I wrote a code for Sheet 1 which actually works...it only works if I manually add the value in the range in Sheet 1, however, when the value is input in Sheet 2 (which then is presented as a total in Sheet 1) the warning does not pop up...
Any help would be greatly appreciated
Thank you in advance!
Here is what I have so far:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("M3:M1827")) Is Nothing Then
If Range("M1829") >= 275000 Then MsgBox "Warning! You have reached or even exceeded the annual authorised limit for Legal Advice expenses.", vbCritical
If Range("M1829") >= 192500 And Range("M1829") < 247500 Then MsgBox "Warning! You have reached the 70% of the annual authorised limit for Legal Advice expenses.", vbInformation
If Range("M1829") >= 247500 And Range("M1829") < 275000 Then MsgBox "Warning! You have reached the 90% of the annual authorised limit for Legal Advice expenses.", vbExclamation
End If
End Sub