Conditional formatting with VBA formula not persisting

I'm using Excel 2007 and I have a VBA module added to my project

Function RegEx(Value As String, Pattern As String, Optional IgnoreCase As Boolean = False)
    Dim r As New VBScript_RegExp_55.RegExp
    r.Pattern = Pattern
    r.IgnoreCase = IgnoreCase
    If r.Test(Value) Then
        RegEx = 1
        RegEx = 0
    End If
End Function

Open in new window

Code references, Microsoft VBScript Regular Expressions 5.5

Using this I added conditional formatting to a whole column. Using the formula option and selecting to fill red when true;

Formula for conditional formatting
=NOT(RegEx(CELL("contents"),"^[A-Za-z0-9 ]{0,20}$"))

Open in new window

When I type a in a cell that's conditionally formatted, a value that would cause the formula to return true ie. "&" and hit Enter or Tab I see the field flase red but return to no formatting. If I edit the field either with a mouse click or F2 and hit Esc, the red formatting is persistent.

Sample of formatting not staying
i think the VBA and Excel formula are correct since the formatting stays when I hit escape, but how can I get it to be formatted if a user presses Enter or Tab.  

Edit: it may be the excel formula part

Open in new window

How do I reference the value of the cell being evaluated?
Who is Participating?
Michael FowlerConnect With a Mentor Solutions ConsultantCommented:
You just need to highlight the entire row and then edit the formula to read
=NOT(RegEx(A1,"^[A-Za-z0-9 ]{0,20}$"))

I have attached a working copy


EdFinnAuthor Commented:
Thanks! This seems to be working. I'd have assumed it would have highlighted the whole column based on the value of A1, but it looks to be formatting each cell based on it's value. Not quite sure why, and would love an explanation on it. But this solves my issue.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.