I run this question again.
I am novice in VBA so I have no idea how to make User Defined Formulas
as written i earlier discussions.

I have cells with formulas picking up data from a linked file.
On and off I overwrite the cell formula with figures (1234).
Then I want the conditional formatting to change the cell color
to remind me that I have overwritten the formula.

I suppose the answer still might be VBA, but can you then explain to a
novice/rookie how to do it?

Regards,
Claes

In VBA, you can use IsNumeric(Range("A1").Formula) to find the status of
cell A1 whether it has a formula or a number. The above returns True or
False.

- Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=274418

Mangesh,

I have no knowledge in VBA and have never worked with it.
I have checked around in the VB Editor in excel.
I see User Form, Module, ClassModule, Procedure(not highlighted)
How and where do I add your IsNumeric(Range("cellref").Formula)
in VBA? And how could this be used in my spreadsheet?

/Claes

"mangesh_yadav" skrev:


In VBA, you can use IsNumeric(Range("A1").Formula) to find the status of
cell A1 whether it has a formula or a number. The above returns True or
False.

- Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=274418

of what I understand from your first post, you need to check if a
particular cell has a formula or a number. And then you want to avoid
the user in accidental editing of a formula. Instead of checking these,
you could simply lock the cell (by default the cells are locked), or
rather, for cells where there are no formulae, you could unlock the
cell by going to FORMAT CELLS PROTECTION and unchecking the LOCKED
box. Then Protect the worksheet by going to TOLS PROTECTION PROTECT
SHEET. Password is optional.

Incase, the above is not helpful, you could write some code to check if
the cell is a formula or number in the module for that sheet.
Right-click on the sheet-name tab and click on view code. This opens
the sheet module. Here for a particular even you can write the code I
mentioned earlier. For instance
IsNumeric(Range("A1").Formula)


- Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=274418

mangesh,

The cell that has a formula picks up a value from a linked file.
I do want to overwrite the cell formula with a number sometimes.
I usually copy a file that has lots of other unique data that I do not want to
rewrite (I have an blank template where all cell formulas have not been
modified).
If I open an old work file and Save as new file and in the old file I have
overwritten formula cells with a number then I would like to visualize that.
In the new file I may want to use the formula but if I do not see that I have
written over the formula I can easily make a mistake and the written number
is used in the calculation.
Then the whole calculation turns out to be wrong.

Sorry but still my knowledge in VBA is very poor.
Should I add this between the lines: (anything else I need to do?)
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
IsNumeric(Range("A1").Formula)
End Sub
How can I use this in my work sheet in a particular cell where I would want
to use conditional formatting to make the cell color yellow in case I have
overwritten the
formula with another plain numeric value.

Regards,
Claes

"mangesh_yadav" skrev:


of what I understand from your first post, you need to check if a
particular cell has a formula or a number. And then you want to avoid
the user in accidental editing of a formula. Instead of checking these,
you could simply lock the cell (by default the cells are locked), or
rather, for cells where there are no formulae, you could unlock the
cell by going to FORMAT CELLS PROTECTION and unchecking the LOCKED
box. Then Protect the worksheet by going to TOLS PROTECTION PROTECT
SHEET. Password is optional.

Incase, the above is not helpful, you could write some code to check if
the cell is a formula or number in the module for that sheet.
Right-click on the sheet-name tab and click on view code. This opens
the sheet module. Here for a particular even you can write the code I
mentioned earlier. For instance
IsNumeric(Range("A1").Formula)


- Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=274418

Hi!

Take a look at this:

http://j-walk.com/ss/excel/usertips/tip045.htm

Biff

-----Original Message-----
I run this question again.
I am novice in VBA so I have no idea how to make User
Defined Formulas
as written i earlier discussions.

I have cells with formulas picking up data from a linked
file.
On and off I overwrite the cell formula with figures
(1234).
Then I want the conditional formatting to change the cell
color
to remind me that I have overwritten the formula.

I suppose the answer still might be VBA, but can you then
explain to a
novice/rookie how to do it?

Regards,
Claes
.

Biff has provided the correct answer for Claes (in my opinion)
but the implementation requires a more complex formula for
the Conditional Formatting than is provided in John's tip 45.

First set up a Defined Name as shown in John Walkenbach's
http://j-walk.com/ss/excel/usertips/tip045.htm
which has to be set up in any workbook it is to be used in.
The advantage of this method is that only Worksheet
Functions are used so it calculates faster than if programming
were used.

The OP (Original Poster) knows which columns normally have
formulas and those are the columns that would be conditionally
formatted (colored). So if Column E is the only column that
would start out with all formulas and the some of the formulas
would be overwritten manually with constants, you would
- Select Column E and if you are at the top then cell E1
would be the active cell. It is the active cell address that
must be used in the formula.
- Format, Conditional Format,
Condition 1, Formula is:
=AND(NOT(CellHasFormula),NOT(ISBLANK(E1)),NOT(ROW( E1)=1))

- Format button, Patterns (tab), choose a light pastel color

John Walkenbach's web page example colors the cells if it has a
formula. Claes wants the opposite, and of course one would not
want to mark the first row with column titles, nor the empty cells
on sheet, beyond the used range. The formula I provided would
not be identifying cells that have been manually wiped out (cleared
out) within the used range -- I hope that would not be a problem.

If there were more than one column this was to be applied to you
would select those columns and write your formula based on the
active cell. For instance my testing was done by selecting ALL
cells (Ctrl+A, except in Excel 2003) and cell A1 was the active cell.

For a better understanding of Conditional Formatting you might also
look at: http://www.mvps.org/dmcritchie/excel/condfmt.htm
---
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

"Biff" wrote in message ...
Hi!

Take a look at this:

http://j-walk.com/ss/excel/usertips/tip045.htm

Biff

-----Original Message-----
I run this question again.
I am novice in VBA so I have no idea how to make User
Defined Formulas
as written i earlier discussions.

I have cells with formulas picking up data from a linked
file.
On and off I overwrite the cell formula with figures
(1234).
Then I want the conditional formatting to change the cell
color
to remind me that I have overwritten the formula.

I suppose the answer still might be VBA, but can you then
explain to a
novice/rookie how to do it?

Regards,
Claes
.

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))
Anyone who knows how to translate the formula for a Swedish version correctly.
I've turned it inside out but nothing works.
GET.CELL (have no translation for that)
INDIRECT is INDIREKT, FALSE is FALSKT.
"rc" is the reference cell to select if I've understood correctly.
48 does not tell me anything.
I have also tried with different dividers i.e. where there is a komma (,) I
tried with
semi-colon (;) or colon(:).

So if someone can translate the formula for Excel97 Swedish version would be
grate. For any commands with Swedish letters ÅÄÖ you can write A an O instead.

Regards,
Claes

"David McRitchie" skrev:

Biff has provided the correct answer for Claes (in my opinion)
but the implementation requires a more complex formula for
the Conditional Formatting than is provided in John's tip 45.

First set up a Defined Name as shown in John Walkenbach's
http://j-walk.com/ss/excel/usertips/tip045.htm
which has to be set up in any workbook it is to be used in.
The advantage of this method is that only Worksheet
Functions are used so it calculates faster than if programming
were used.

The OP (Original Poster) knows which columns normally have
formulas and those are the columns that would be conditionally
formatted (colored). So if Column E is the only column that
would start out with all formulas and the some of the formulas
would be overwritten manually with constants, you would
- Select Column E and if you are at the top then cell E1
would be the active cell. It is the active cell address that
must be used in the formula.
- Format, Conditional Format,
Condition 1, Formula is:
=AND(NOT(CellHasFormula),NOT(ISBLANK(E1)),NOT(ROW( E1)=1))

- Format button, Patterns (tab), choose a light pastel color

John Walkenbach's web page example colors the cells if it has a
formula. Claes wants the opposite, and of course one would not
want to mark the first row with column titles, nor the empty cells
on sheet, beyond the used range. The formula I provided would
not be identifying cells that have been manually wiped out (cleared
out) within the used range -- I hope that would not be a problem.

If there were more than one column this was to be applied to you
would select those columns and write your formula based on the
active cell. For instance my testing was done by selecting ALL
cells (Ctrl+A, except in Excel 2003) and cell A1 was the active cell.

For a better understanding of Conditional Formatting you might also
look at: http://www.mvps.org/dmcritchie/excel/condfmt.htm
---
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

"Biff" wrote in message ...
Hi!

Take a look at this:

http://j-walk.com/ss/excel/usertips/tip045.htm

Biff

-----Original Message-----
I run this question again.
I am novice in VBA so I have no idea how to make User
Defined Formulas
as written i earlier discussions.

I have cells with formulas picking up data from a linked
file.
On and off I overwrite the cell formula with figures
(1234).
Then I want the conditional formatting to change the cell
color
to remind me that I have overwritten the formula.

I suppose the answer still might be VBA, but can you then
explain to a
novice/rookie how to do it?

Regards,
Claes
.

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))

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))

David,

I just came across this thread and had an additional question..I see how to
conditionally format cells that have formulas, but is there a way to put a
different format on cells that are not only formulas, but have external
workbook references..

For example, if it is an internal workbook referenced formula, i would just
change the color of the numbers to BLUE, but if the formula contained and
external reference to another workbook, i would want the cell to be colored
ORANGE, if the number is a hard code, make the cell GREEN..

Any suggestions without writing VBA macros? or if a macro is the only
way..can you provide as i don't know VBA at all.

thanks in advance for your assistance.
--
Mike


"David McRitchie" wrote:

Biff has provided the correct answer for Claes (in my opinion)
but the implementation requires a more complex formula for
the Conditional Formatting than is provided in John's tip 45.

First set up a Defined Name as shown in John Walkenbach's
http://j-walk.com/ss/excel/usertips/tip045.htm
which has to be set up in any workbook it is to be used in.
The advantage of this method is that only Worksheet
Functions are used so it calculates faster than if programming
were used.

The OP (Original Poster) knows which columns normally have
formulas and those are the columns that would be conditionally
formatted (colored). So if Column E is the only column that
would start out with all formulas and the some of the formulas
would be overwritten manually with constants, you would
- Select Column E and if you are at the top then cell E1
would be the active cell. It is the active cell address that
must be used in the formula.
- Format, Conditional Format,
Condition 1, Formula is:
=AND(NOT(CellHasFormula),NOT(ISBLANK(E1)),NOT(ROW( E1)=1))

- Format button, Patterns (tab), choose a light pastel color

John Walkenbach's web page example colors the cells if it has a
formula. Claes wants the opposite, and of course one would not
want to mark the first row with column titles, nor the empty cells
on sheet, beyond the used range. The formula I provided would
not be identifying cells that have been manually wiped out (cleared
out) within the used range -- I hope that would not be a problem.

If there were more than one column this was to be applied to you
would select those columns and write your formula based on the
active cell. For instance my testing was done by selecting ALL
cells (Ctrl+A, except in Excel 2003) and cell A1 was the active cell.

For a better understanding of Conditional Formatting you might also
look at: http://www.mvps.org/dmcritchie/excel/condfmt.htm
---
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

"Biff" wrote in message ...
Hi!

Take a look at this:

http://j-walk.com/ss/excel/usertips/tip045.htm

Biff

-----Original Message-----
I run this question again.
I am novice in VBA so I have no idea how to make User
Defined Formulas
as written i earlier discussions.

I have cells with formulas picking up data from a linked
file.
On and off I overwrite the cell formula with figures
(1234).
Then I want the conditional formatting to change the cell
color
to remind me that I have overwritten the formula.

I suppose the answer still might be VBA, but can you then
explain to a
novice/rookie how to do it?

Regards,
Claes
.