EUREKA! (But I won't run out naked through the streets)

Thanks David.
Lots of useful info on the linked sites, thanks.

It turned out to be easier than I thought it would be.
I made like this:

I entered the VBA editor, added a module in VBA and pasted your code:
Function HasFormula(cell)
HasFormula = cell.HasFormula
End Function

Back to the Exel Worksheet.
Then I could pick up the function (fx) in section (Sw: Anpassade) User
Defined Functions. I added the function "HasFormula" in Cell E3 to check B3
to get the result True or False in Cell E3.
Then I used Conditional Format(CF) on Cell B3 with Folmula is: =E3=FALSE and
used a yellow pattern for the CF. When overwriting the formula with a
constant in Cell B3 the Cell turns yellow since the Cell E3 will have the
result FALSE and this exactly the way I wanted it to work.

Then its just a matter of copying the formulas and copying the CF for
further rows with cells. And it works on each individual cells.

Below shows the example in Excel. I have written the Formulas. First section
below is Swedish, second section is English, third is how the result looks.
You have to imagine B4, B5, B6 turning yellow.

1 B C D E
2 Formula or constant (Swedish)
3 =SUMMA(C3;D3) 1 1 =HasFormula(B3)
(CF i Cell B3) Formel är=E3=FALSKT
4 2(constant) 2 2 =HasFormula(B4)
(CF i Cell B4) Formel är=E4=FALSKT
5 3(constant) 2 2 =HasFormula(B5)
(CF i Cell B5) Formel är=E5=FALSKT
6 5(constant) 2 2 =HasFormula(B6)
(CF i Cell B6) Formel är=E6=FALSKT

7 Formula or constant (English)
8 =SUM(C8;D8) 1 1 =HasFormula(B8)
(CF in Cell B8) Formula is=E8=FALSE
9 2(constant) 2 2 =HasFormula(B9)
(CF in Cell B9) Formula is=E9=FALSE
10 3(constant) 2 2 =HasFormula(B10)
(CF in Cell B10) Formula is=E10=FALSE
11 5(constant) 2 2 =HasFormula(B11)
(CF in Cell B11) Formula is=E11=FALSE

Result Swedish English
B-Col. C-Col. D-Col. E-Col. E-Col.

Row 3 2 1 1 SANT TRUE
Row 4 2(yel) 2 2 FALSKT FALSE
Row 5 3(yel) 2 2 FALSKT FALSE
Row 6 5(yel) 2 2 FALSKT FALSE

Thanks to everyone who have given their supportive help in this matter.

The only thing now is that I get the note that there is a Macro when opening
the file and the question if I want to activate or de-activate it.
I have Excel2002 at home and Medium Security. (Excel97 at work as mentioned
earlier)
Is there some way to avoid this pop-up without setting the security to low?

Regards,
Claes

"David McRitchie" skrev:

Hi Claes,
GET.CELL is from Excel 4 you might try translations
http://cherbe.free.fr/traduc_fonctions_xl97.html

If you refer to my page on Conditional Formatting (look for HasFormula)
http://www.mvps.org/dmcritchie/excel/excel.htm
you could use the user defined function HasFormula for this, but since you have
to install the function in the same workbook might just change that
so that it excludes row 1 and excludes ISBLANK from returning True.


Function cf_NotFormula(cell)
'based on http://www.mvps.org/dmcritchie/excel...htm#hasformula
cf_NotFormula = Not cell.HasFormula And Not IsEmpty(cell) _
And Not cell.Row = 1
End Function

Then your Conditional Formatting Statement would be:
Condition 1: =cf_NotFormula(A1)

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Claes G" wrote ...
I have Excel 97 Swedish version and I tried Biff's formula but I can not
get it to work.
=GET.CELL(48, INDIRECT ("rc", FALSE))