Prevent Saving File Based on Value in a Column

[xlhelppls]

Hello - I have a file where users enter in codes (col j) and they must only be a length of 10. I am using a formula (in col I) to check if they are length of 10, otherwise it will color the cell red and put in word "invalid." I want to prevent users from saving the file until they fix any codes that are not a length of 10 OR said another way, if the word "invalid" is present in any cells in Col I, then I don't want them to be able to save it until they do so.

I have searched for some posts which appear to be doing this, and tried to make relevant updates to it to fit my case, but I new to VB and keep getting errors. here is the code I am using:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Worksheets("Sheet1").Range("I20:I50") = "invalid" Then
    Cancel = True
    MsgBox "You cannot save until the code is in the right format", vbOKOnly
End If
End Sub

the error I am getting is:
run time error 13
type mismatch

and the line it highlights is:
If Worksheets("Sheet1").Range("I20:I50") = "invalid" Then

I thought I had the right updates made, but not sure what else needs to be changed?
any help is much appreciated!

 

[Joe4]

You cannot look for a value across a whole range of cells like that.
Try this instead:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Application.WorksheetFunction.CountIf(Worksheets("Sheet1").Range("I20:I50"), "invalid") > 0 Then
    Cancel = True
    MsgBox "You cannot save until the code is in the right format", vbOKOnly
End If
End Sub

 

[xlhelppls]

Joe4 - THANK YOU SO MUCH!
appreciate the quick reply and I tried it - works fabulously!
have a great day!!

 

[Joe4]

You are welcome!
Glad I was able to help!