Excel Formula Question 2

[dianemarie]

Hello, I would like to do a conditional formatting formula that says if the cell immediately to the left of B1 is a formula, highlight B1, otherwise don't highlight. Can I do this? Thank you for any help.

 

[luceze]

You have to use a user defined function to accomplish this.

In a standard module place this code:

Function GetFormula(cell As Range)
GetFormula = cell.HasFormula
End Function

Then in your conditional formatting for cell B1, for condition one, change it to "Formula is" and enter this formula:
=Getformula($A$1)

HTH,
Eric

 

[ilses]

Hi Diane,

One way is to create a custom function like

Function HASFORMULA(cell As Range) As Boolean
HASFORMULA = cell.HasFormula
End Function

and then use

=HASFORMULA($A$1)

as Conditional Format formula.

Ilse

 

[byundt]

You can do it with a named formula.
1) Open the Insert...Name...Define menu item
2) In the "Names in workbook" field, enter a name like FormulaToLeft
3) In the "Refers to" field, enter the formula:
=GET.CELL(48,INDIRECT("rc[-1]",FALSE))
4) Click OK
5) Select the cells to receive Conditional Formatting
6) Open the Format...Conditional Formatting menu item
7) Choose "Formula is", then enter:
=FormulaToLeft
8) Click the Format button, then click the Patterns tab and choose a highlight color
9) Click OK twice

 

[KenWright]

Just a note of caution - The following is from one of a series of notes posted by Harlan Grove out on the MS groups in response to the use of GET.CELL.

"Gerald McInally" <gerald.mcinally@akermar.com> wrote...
>I am trying to use the GET.CELL function to work in
>Excel97.
>I have followed the instructions in article Q151449 from
>the MS knowlege base, but I always get an 'error in
>formula' message.
>Is there something I have to install or do to get this
>function (or is it a macro?) to work?
...

One thing the nice KB article DOESN'T mention is that if you copy any range
containing a cell formula dereferencing a defined name referring to an XLM
macro function and try to paste into another worksheet, you'll CRASH Excel
97 and 2K (others have confirmed that this has been fixed in XP). CRASH as
in Excel throws an exception and DIES, giving you NO CHANCE to save your
work. If you like unnecessary thrills, go for it. Otherwise wrap your XLM
calls inside VBA udfs (or dispense with XLM entirely and just use VBA udfs).

Regards
Ken.............


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[dianemarie]

The UDF worked. Thank you both. Byundt, I had already accomplished the task before I saw your post, but it looks interesting. Thank you also.