Results 1 to 8 of 8

Macro will not allow conditional formatting with a user-defined function

Threaded View

Med_MV Macro will not allow... 09-13-2013, 04:06 PM
JBeaucaire Re: Macro containing... 09-14-2013, 11:56 AM
Med_MV Re: Macro containing... 09-16-2013, 10:19 AM
JosephP Re: Macro will not allow... 09-16-2013, 10:28 AM
Med_MV Re: Macro will not allow... 09-16-2013, 12:20 PM
JBeaucaire Re: Macro will not allow... 09-16-2013, 02:01 PM
Med_MV Re: Macro will not allow... 09-16-2013, 02:28 PM
JBeaucaire Re: Macro will not allow... 09-16-2013, 05:28 PM
  1. #1
    Registered User
    Join Date
    09-13-2013
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    5

    Macro will not allow conditional formatting with a user-defined function

    Problem: I want to hi-light all cells that contain a formula - but by using a macro, so that I can later un-hi-light that same range. In other words, I want to easily see where formulas have replaced hard numbers, and then revert back just as quickly. I realize that there is a "Show Formulas" menu option, but it is messy, and it does not make the relevant cells any more visible.

    Currently, I can hi-light the cells by creating the following user-defined function:

    Function IsFormula(cell_ref As Range)
        IsFormula = cell_ref.HasFormula
    End Function
    ...and then using conditional formatting over a range, using the "formula to determine which cells...." as follows:

    =IsFormula(A1) ..... [applied to a range, of course]

    This works great. But now I want to have a macro (button) create that condition, with another macro button to delete the condition. Excel will allow a macro to delete conditions just fine. And Excel will allow a macro to create conditions based on cell values, etc. But Excel does not seem to allow a macro to create a condition based on a user-defined function.

    If I record a macro of my steps, I get the following:

    Sub Hi_Light_Formulas()
        Range("A1:Z999").Select
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=isformula(A1)"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
        End With
        Selection.FormatConditions(1).StopIfTrue = False
    End Sub
    The problem is that nothing happens. When I check the conditional formatting rules, it says "No Format Set."

    Any help would be appreciated.
    Last edited by Med_MV; 09-14-2013 at 02:18 PM. Reason: As per Forum Rule #3, please use code tags…Thanks.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Using the MOD function in a "User" defined macro
    By Vba Novice in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-11-2013, 04:20 PM
  2. user defined macro to replace certain characters in a string based on user input
    By whatappears in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-17-2012, 06:25 PM
  3. Access connection works in Macro, not in user-defined Function
    By Steve in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-27-2005, 10:05 AM
  4. How to: User Form to assign a user defined range to a macro variab
    By TrevTrav in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-22-2005, 04:06 PM
  5. Macro user defined function row information
    By bj in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-09-2005, 11:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1