Explanation of formula

There is a clever piece of conditional formatting designed to show if a formula has been overwritten. It works by creating a range called (say) CellHasFormula and then entering =GET.CELLl(48,Indirect("rc",false)) in the reference box. In the cell you wish to conditionally format you enter =CellHasFormula. If the formula in that cell is overwritten, then the conditional formatting is displayed.

Can someone explain to me how it works?

Thanks

Good morning BRISBANEBOB

This technique that you are describing is actually an old (OK, very old) XL4 macro. These are the days before VBA was invented, and actually predates my time with Excel that started with Excel 95.

The problem with these old XL4 macros is that they can't be used directly in the worksheet - which is why you need to wrap the formula up in a named range. The object of the Get.Cell command is to return info about a particular cell, in pretty much the same way as todays Cell() command does - except far more powerful.

The problem with these XL4 macros is that they are followed by parameters that are usually just numeric, so unless used frequently (very frequently) you wouldn't be able to follow what's happening. These old XL4 macros still pop up (albeit infrequently) on the forums because in some cases they can return information that can only be accessed by using a number of lines of VBA code.

If you really need to know more, have a look at this link which contains the old help files for XL4 functions.

HTH

DominicB

Hi BRISBANEBOB

Originally Posted by BRISBANEBOB

Thanks for the explanation. Is there a 'modern' equivalent which achieves the same result? i.e. uses conditional formatting to show if a cell does not have a formula in it?

Try putting the code below in as a function :

Function IsFormula(Rng As Range) As Boolean
Application.Volatile
IsFormula = Rng.HasFormula
End Function

Now, select the cell you want to format, and go to Format > Conditional Formatting. Select FormulaIs and put this formula in :
=IsFormula(A1)=TRUE
and select your format. Now when A1 contains a formula your formatting will be applied. When it doesn't it won't.

HTH

DominicB

Edit : Before anyone points it out, you don't actually need the =True bit - I usually just show it to help the user follow what's going on.

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.