highlight/color cells with specific character inside

[markx]

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

 

[Bob Phillips]

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]

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

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)

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

 

[Guest]

Hi markx:

Bob's approach to conditionally highlighing formulae is probably the best.
Looking for the = symbol will lead you to treat the formula:
=A1+B1
the same as the text:
Hard Work = Success

Looking for the ! symbol will lead you to treat the linkage formula:
=Sheet1!$A$1
the same as the text:
Have a good day!!!
--
Gary''s Student

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

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)

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

 

[Bob Phillips]

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)

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

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)

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

 

[markx]

Wow Bob,
Thank you very much, it's excellent!

However, I wouldn't be myself if I didn't ask some additional questions :

Do you also know how should I modify this macro in order to make the
"formula research" not only on the current sheet, but on the whole workbook?
(suppose I have to change "Set this = ActiveSheet" to something like "Set
this = ActiveWorkbook", but I'm not sure if it's enough...)

Also, do you have an idea how to create an additional column on the
"FormulaeList" worksheet that will give me the addresses of the cells with
formulas (and not only the sole formulas)?

Thanks again for your time and involvement,
I appreciate this a lot,
Mark

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)

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

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)

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

 

[Bob Phillips]

Mark,

This should address both points

Sub FindFormulae()
Dim this As Worksheet
Dim sh As Worksheet
Dim cell As Range
Dim i As Long

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 this In ActiveWorkbook.Worksheets
If this.Name <> sh.Name Then
For Each cell In this.UsedRange
If cell.HasFormula Then
i = i + 1
sh.Cells(i, "A").Resize(, 3).Interior.ColorIndex =
xlColorIndexNone
Select Case True
Case cell.Formula Like "*]*"
sh.Cells(i, "A").Interior.Color = vbRed
cell.Interior.Color = vbRed
Case cell.Formula Like "*!*"
sh.Cells(i, "A").Interior.Color = vbBlue
cell.Interior.Color = vbBlue
Case Else
sh.Cells(i, "A").Interior.Color = vbGreen
cell.Interior.Color = vbGreen
End Select
sh.Cells(i, "B").Value = "'" & cell.Formula
sh.Cells(i, "C").Value = cell.Parent.Name & "!" &
cell.Address
End If
Next cell
End If
Next this

sh.Columns("A:C").AutoFit
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

Wow Bob,
Thank you very much, it's excellent!

However, I wouldn't be myself if I didn't ask some additional questions :

Do you also know how should I modify this macro in order to make the
"formula research" not only on the current sheet, but on the whole workbook?
(suppose I have to change "Set this = ActiveSheet" to something like "Set
this = ActiveWorkbook", but I'm not sure if it's enough...)

Also, do you have an idea how to create an additional column on the
"FormulaeList" worksheet that will give me the addresses of the cells with
formulas (and not only the sole formulas)?

Thanks again for your time and involvement,
I appreciate this a lot,
Mark

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)

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





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)

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