+ Reply to Thread
Results 1 to 5 of 5

Explanation of formula

Hybrid View

BRISBANEBOB Explanation of formula 04-01-2009, 09:19 PM
dominicb Re: Explanation of formula 04-02-2009, 04:23 AM
BRISBANEBOB Re: Explanation of formula 04-02-2009, 06:29 PM
dominicb Re: Explanation of formula 04-03-2009, 03:28 AM
DonkeyOte Re: Explanation of formula 04-03-2009, 03:44 AM
  1. #1
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Explanation of formula

    FWIW, I think most people would argue that given Conditional Formatting is (Super) Volatile (as is the UDF) you are often best served using a Worksheet_Change event to test if the altered (cell(s)) contain a formula or not and formatting the target (cells) accordingly... eg

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngCell As Range
    For Each rngCell In Target.Cells
        rngCell.Interior.ColorIndex = IIf(rngCell.HasFormula, xlNone, 3)
    Next rngCell
    End Sub
    Or you can make use of the often under utilised SpecialCells(xlCellTypeFormulas) method:

    Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        .Interior.ColorIndex = 3
        .SpecialCells(xlCellTypeFormulas).Interior.ColorIndex = xlNone
    End With
    End Sub
    No need to iterate the above... all cells in the Target range are defaulted to Red and then subsequently any formulae cells present within the range have the background colour removed.
    Last edited by DonkeyOte; 04-03-2009 at 03:46 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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