Conditional formatting with VBA formula not persisting

Posted on 2011-04-28
Last Modified: 2013-11-05
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?
Question by:EdFinn
    LVL 23

    Accepted Solution

    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



    Author Comment

    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.


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    excel formula or VBA 7 36
    Detect file exist or not 3 40
    VBA to sum a column 13 30
    .xltm file opens as .xlsx file 3 0
    Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now