• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 737
  • Last Modified:

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:
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
CELL("contents")

Open in new window

How do I reference the value of the cell being evaluated?
0
EdFinn
Asked:
EdFinn
1 Solution
 
Michael FowlerSolutions 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

Michael


test.xlsm
0
 
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.

Thanks.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now