Link to home
Start Free TrialLog in
Avatar of EdFinn
EdFinnFlag for United States of America

asked on

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
    Else
        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.

Example:
User generated image
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")

Open in new window

How do I reference the value of the cell being evaluated?
ASKER CERTIFIED SOLUTION
Avatar of Michael Fowler
Michael Fowler
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of EdFinn

ASKER

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.

Thanks.