EdFinn
asked on
Conditional formatting with VBA formula not persisting
I'm using Excel 2007 and I have a VBA module added to my project
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.
Example:
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
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
Else
RegEx = 0
End If
End Function
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}$"))
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.
Example:
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
CELL("contents")
How do I reference the value of the cell being evaluated?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks.