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
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
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.
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
How do I reference the value of the cell being evaluated?