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

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
.

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

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
.

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

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

Hi Claes,
The object is to color the cells in Column B that do not have formulas.
But you do not want to color cell B1 because if is in Row 1,
and you do not want to color cells that are empty.

Whatever you are doing is not exactly what I suggested, there would
be no extra formulas added to the page that you can see.

If you are using a User Defined Function you have no need to use a defined name.
Use of the defined name was strictly to allow you to use an Excel 4 type of
function.

But you do have the part about the formulas in the Conditional Formatting
are the same formulas that you use on the worksheet.

remove the defined names you created it will mess you up, since
you are now using a user defined function.

Select the column(s) you want the coloring to apply to
which appears to be Column B so after selection of
column B, Cell B1 should be the active cell and will be
the cell you use in the Conditional Formula, even if you have
more than one column selected it the active cell that is used
in the formula. I am assuming you will select entire columns.

I had suggested a modification of the HasFormula UDF, but
we'll go with the original HasFormula user defined function.

Format, Conditional Formatting, cells
condition 1
Formula is:
=AND(NOT(HASFORMULA(B1)),NOT(ISBLANK(B1)),NOT(ROW( B1)=1))

If you used $B1 instead of B1 then all cells selected
when you entered your conditional formatting would be based
on the column B value in each row.

My page on Conditional Formatting:
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

"Claes G" wrote in message news:2524ECD0-CCDB-450F-938B-

Hi David,

OK, I got your point and I include the Swedish formula corresponding to yours:

Format, Conditional Formatting, cells
condition 1
Formula is:
=AND(NOT(HASFORMULA(B1)),NOT(ISBLANK(B1)),NOT(ROW( B1)=1))

Swedish "Formula is:"
=OCH(ICKE(HasFormula(B1));ICKE(ÄRTOM(B1));ICKE(RA D(B1)=1))

Above works as you say and will only affect cells that is written a constant
into,
cells with formula and empty cells are not affected.

In my case (another file, and has nothing to do with the example I showed)
I have formulas in each cell in lets say B1 to B30.
(E.g. In B1 I have LOOKUP A1 and collect value from a linked file)
I wanted to show whenever a formula in each cell B1 to B30 had been
overwritten by a constant or deleted (i.e. made empty).
The cells B1-B30 are part in a calculation and should normally always
have the formula to calculate from.
The value in B1 is collected from a linked file and depends on
the code written into A1, same for B2 corresponding to the code written into
A2 and so on.
The code written into A1 to A30 will be vary in each new workfile.
On those occasion I have written a constant in e.g. B1 I have overrided
the Formula and if I copy that file to a new one and write a new code in A1
it will be the constant in B1 that it is calculated with, not the value
collected from the linked file.
Thats why I want to show clearly that the formula is overwritten or deleted
in any of the cells from B1-B30.

Sorry that I may not have been very clear on this point.

The example I showed in my last contribution now works for my purposes.

Any answer for below question?
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?

Kind regards,
Claes

Hi Everyone,

It turns out that Bill's Excel tips was right after all.
The tips that actually was submitted by David Hager according the
information on the linked site Bill had given.
That tip does not require any VBA, so you will not get any message, when
opening a file, that the file contains a Macro: Wish to activate or
de-activate it.

So, David, I have got my solution without VBA to work now and you don't need
to answer my last question.

As I am using Excel with Swedish languague it is neccessary to translate
formulas from english to swedish. Thanks to David McRitchie I got the link to
a site with a translation table in lots of different languagues.

This will be my last contribution to this thread.

Following Bill's link you will find the tips with header "Identify formulas
using Conditional Formatting"

I will write below the English and Swedish version of formulas. For any
other languague find the link from David McRitchie to see how you have to
convert the formulas. English formula uses comma where Swedish uses
semi-colon.
1. Select: Insert, Name, Define
2. In the define Name Dialog box, enter the following in the 'Names in the
workbook' box:
CellHasFormula (You may write another name, but to make it easier following
the instructions below write the name as shown)
3. Enter the formula in 'Refers to' box:
English
=GET.CELL(48,INDIRECT("rc",FALSE))
Swedish
=HÄMTA.CELL(48;INDIREKT("rc";FALSKT))
(Above formula must be written exactly as shown, with exception to other
languagues commands and divider usage)
4. Click Add, and then OK.

Conditional Formatting (CF)
If you want CF to change something in a cell that has a formula when beeing
overwritten by a constant or beeing deleted then use CF and add this into CF
Fomula is:
English
=NOT(CellHasFormula)
Swedish
=ICKE(CellHasFormula)
(You can mark/select the whole column or row if you like)

If you want CF to change something in a cell that has a formula when beeing
overwritten by a constant only, i.e. if cell is deleted it will not use CF,
then add this into CF
Fomula is:
English
=AND(NOT(CellHasFormula),NOT(ISBLANK(A1)))
Swedish
=OCH(ICKE(CellHasFormula);ICKE(ÄRTOM(A1)))

A1 refers to the actual marked/selected cell, could be any other cell in
your case.
With this CF method its easier to mark one specific cell and add the formula.
When done you can use the copy format button to apply the format to other
cells.

Good Luck everyone and thanks again to those who have given their
supportive help in this matter.

Kind regards,
Claes G

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
.