Msgbox to pop up only once considering multiple errors in column

[jharih1]

Good afternoon,

I looked through multiple posts on the forum boards, but something is not working properly with my code. I was hoping some of you VB experts can help me.

What I wish for it to do = look up errors in a column, highlight the erroneous cells red, and popup a msgbox once saying "Please enter valid month".

I created a public sub function to do the lookups..I think the code will speak to itself:

Private Sub Worksheet_Change(ByVal Target As Range)


'Checks if vlookup function returns an "ERROR" for all data validation and vendor number fields.
'If it is blank or has valid data, no conditional formatting will be used.
'If checks = "Error" then cell should be highlighted and a msgbox will popup requesting user to enter valid info.


Check_Month
End Sub

Public Sub Check_Month()
Dim Warning As Boolean


For i = 2 To 9999


    If Cells(i, 22).Value = "ERROR" Then
       Cells(i, 1).Interior.Color = RGB(198, 30, 2)
       MsgBox "Please enter valid Month"
       Warning = True
    Else:
       Cells(i, 1).Interior.ColorIndex = 0
       Warning = False
    End If
    
Next i


End Sub

Please help! I hate VB now

 

[jharih1]

And this will only give 1 msgbox or I would still have to dim a boolean?

 

[Fluff]

Yup, just the one msgbox

 

[jharih1]

Cool! I will try it out

Yup, just the one msgbox

 

[jharih1]

So this morning, I had one of my coworkers download the spreadsheet from sharepoint and test on her computer. We keep getting a 1004 VB Error, and the debug is pointing at

Public Sub Check_Month()
Dim Warning As Boolean


Warning = False


For i = 2 To 9999


    If Cells(i, 22).Value = "ERROR" Then
[COLOR=#ff0000]       Cells(i, 1).Interior.Color = RGB(198, 30, 2)[/COLOR]
       Warning = True
    Else:
[COLOR=#ff0000]       Cells(i, 1).Interior.ColorIndex = 0[/COLOR]
    End If


Next i


If Warning Then
    MsgBox "Please enter valid Month"
End If


End Sub

Any suggestions why? It was working Friday when the spreadsheet was housed on my local machine, but after posting to Sharepoint - there are a few bugs. 1) if I copy paste info in any field, it automatically locks that particular cell (when it originally is set to unlocked), and errors out on this info. I wished to protect the spreadsheet.