Mark,

How about this macro

Sub FindFormulae()
Dim this As Worksheet
Dim sh As Worksheet
Dim cell As Range
Dim i As Long
Set this = ActiveSheet
On Error Resume Next
Set sh = Worksheets("FormulaeList")
On Error GoTo 0
If sh Is Nothing Then
Set sh = Worksheets.Add
sh.Name = "FormulaeList"
Else
sh.Cells.ClearContents
End If
For Each cell In this.UsedRange
If cell.HasFormula Then
i = i + 1
sh.Cells(i, "A").Value = "'" & cell.Formula
Select Case True
Case cell.Formula Like "*]*"
sh.Cells(i, "A").Offset(0, 1).Interior.Color = vbRed
cell.Interior.Color = vbRed
Case cell.Formula Like "*!*"
sh.Cells(i, "A").Offset(0, 1).Interior.Color = vbBlue
cell.Interior.Color = vbBlue
Case Else
sh.Cells(i, "A").Offset(0, 1).Interior.Color = vbGreen
cell.Interior.Color = vbGreen
End Select
End If
Next cell
sh.Columns(1).AutoFit
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"markx" wrote in message
...


The answer I received before was basically to put "conditional formatting"
on cells and specify that if cell value is equal to "my value", than it
should format it in a specific way.
Of course, it works only if "my value" is the only value in the analysed
cell (and is not a part of a larger string), and that's the problem.

To respond to your question, I'm really looking for something very
general,
as described below (highlighting/coloring all cells where a particular
character is placed). Then, of course, I can apply it to my curret
specific
needs, which are finding:
- simple formula (character "=") - a cell responding to this condition
could be colored in green
- formula relating to another worksheet (characters "=" and "!") - a cell
responding to this condition could be colored in yellow
- formula relating to another workbook (characters "=", "[", "]" and
".xls") - a cell responding to this condition could be colored in red

It would be also great (hope it's not too complicated) to create a new
worksheet where all these formulas are listed, f. ex. in the following
format:

Cell Address: Formula in cell address refering to:
Sheet1!A2 '=Sheet1!B4
Sheet1!M15 '=Sheet5!F122
Sheet2!B3 '=[Other_file.xls]Sheet4!B4
Sheet4!F14 '=Sheet1!B5
Sheet6!AD12 '=Sheet1!B5

but you can leave this last feature (it's not essential).

Thanks for any hints,
Mark





"Bob Phillips" wrote in message
...
Mark,

I don't know what answers that you got previously, but have you tried a
simple UDF, like so

Function IsFormula(rng As Range)
IsFormula = rng.HasFormula
End Function


and use that in the CF.

Or are you looking for just external reference formulae?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"markx" wrote in message
...
Hi guys,

Some time ago, I posted the question below, but didn't receive any 100%
satisfactory answer. Could you take a look at this? Thanks!

-------------
What is the best way to highlight (or even color) all the cells in a
worksheet (or even in a workbook) containing a specific character? For
exemple, I would like to make visually distinctive all the cells
containing
somewhere a "?" character (or "mmm" string, or "!" mark, or "=" sign).

Are you aware of any special easy macro doing (and also undoing) this
kind
of stuff?
Or do you have idea how to write a special formula to put in
"conditionnal
formatting" part?
-------------
(To be more precise, I would like to find not only the cells where the
reqested sign is the only one (f. ex. "?" in cell A1="?"), but also the
cells where this sign is one parmi others (like f. ex. finding "?" in
cell
A2="mxz?wptex").

* * *
One of my goals is to find (through this method) all the formulas (as
they
contain "=" sign), references to other sheets (looking for the "!"
sign)
and
worksheets ("[", "]" and ".xls" signs). Please let me know if there is
any
other (simplier and more reliable) way to do this...

Once again, many thanks folks!
Mark