How to select Formula cells with red interior?
 

2003

Is there a way to select all formula cells with i.e. a red interior color in one selection?

I do know the following:
Set MyRange = Selection.SpecialCells(xlFormulas, 23)
But without processing all MyCells in MyRange individually, can I
select ALL formula cells with a red interior then change ALL to
yellow?

Thanks

EagleOne

How to select Formula cells with red interior?
 

Nope, you need to go find em.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

<donoteventry; <removes; wrote in message
...
2003

Is there a way to select all formula cells with i.e. a red interior color
in one selection?

I do know the following:
Set MyRange = Selection.SpecialCells(xlFormulas, 23)
But without processing all MyCells in MyRange individually, can I
select ALL formula cells with a red interior then change ALL to
yellow?

Thanks

EagleOne

How to select Formula cells with red interior?
 

Interesting Bob, did you literally mean Find()? or,
For Each MyCell in My Range ?

Thanks


"Bob Phillips" wrote:

Nope, you need to go find em.

How to select Formula cells with red interior?
 

In the line:
Selection.SpecialCells(xlFormulas, 23)
What does the 23 indicate?
Where can I find the Numbers listed with meaning in Help?
I looked but was unable to find - even searching Google?
I also saw Selection.SpecialCells(xlFormulas, 16)
TIA,
Jim

"
wrote in message
:

Interesting Bob, did you literally mean Find()? or,
For Each MyCell in My Range ?

Thanks


"Bob Phillips" wrote:

Nope, you need to go find em.

How to select Formula cells with red interior?
 

You might try doing this via Word.

"donoteventry" <donoteventry, "removes"" wrote:

2003

Is there a way to select all formula cells with i.e. a red interior color in one selection?

I do know the following:
Set MyRange = Selection.SpecialCells(xlFormulas, 23)
But without processing all MyCells in MyRange individually, can I
select ALL formula cells with a red interior then change ALL to
yellow?

Thanks

EagleOne

How to select Formula cells with red interior?
 

With xl2002+, excel added a way to replace the formatting of cells, too. You
can see it under the Options button on the edit|Replace dialog.

In code:
Option Explicit
Sub testme()

Dim myRng As Range

With ActiveSheet
Set myRng = Nothing
On Error Resume Next
Set myRng = .Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No formulas in sheet!"
Exit Sub
End If

With Application.FindFormat
.Clear
.Interior.ColorIndex = 3
End With

With Application.ReplaceFormat
.Clear
.Interior.ColorIndex = 27
End With

.Cells.Replace What:="", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=True, ReplaceFormat:=True

End With

End Sub

In my workbook, red had a colorindex of 3 and bright yellow is 27. These may
differ for you. You can record a macro while changing the fill color to see
what your workbook uses. (Colors travel with the workbook, so it could be
different for each workbook--if someone customized the color scheme.)



wrote:

2003

Is there a way to select all formula cells with i.e. a red interior color in one selection?

I do know the following:
Set MyRange = Selection.SpecialCells(xlFormulas, 23)
But without processing all MyCells in MyRange individually, can I
select ALL formula cells with a red interior then change ALL to
yellow?

Thanks

EagleOne

--

Dave Peterson

How to select Formula cells with red interior?
 

The best way is to use the same method that you
colored them in the first place; however, you can use
a macro to check every cell individually (time consuming)
Counting cells based on interior or font color
http://www.cpearson.com/excel/colors.htm

For directions in installing and using a macro see
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Some additional examples using Chip Pearson's macros
Interior Color, using Count, SUM, etc. (#count)
http://www.mvps.org/dmcritchie/excel/colors.htm#count

And you also specifically asked about Special Cells
Select cells with either formulas or constants (#specialcells)
http://www.mvps.org/dmcritchie/excel...m#specialcells

Some notations on the above code, and use of Special Cells (speed
and efficiency considerations) « (#notations)
http://www.mvps.org/dmcritchie/excel....htm#notations
---
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

<donoteventry; <removes; wrote in message ...
2003

Is there a way to select all formula cells with i.e. a red interior color in one selection?

I do know the following:
Set MyRange = Selection.SpecialCells(xlFormulas, 23)
But without processing all MyCells in MyRange individually, can I
select ALL formula cells with a red interior then change ALL to
yellow?

Thanks

EagleOne

How to select Formula cells with red interior?
 

Highlight "specialcells" in your code and hit F1.

Then click on "XlSpecialCellsValue" in the Value description.

You'll see:

XlSpecialCellsValue can be one of these XlSpecialCellsValue constants.
xlErrors
xlLogical
xlNumbers
xlTextValues

If you type this in the immediate window:
?xlErrors
you'll see xlErrors is a constant for 16.

Do the same for all 4 constants. Then add those numbers. You'll see the
connection.

If you record a macro when you do:
edit|goto|special|check formulas
and change some of the options, you'll see other numbers in the code. I bet
you'll see how the recorder got it.


Jim May wrote:

In the line:
Selection.SpecialCells(xlFormulas, 23)
What does the 23 indicate?
Where can I find the Numbers listed with meaning in Help?
I looked but was unable to find - even searching Google?
I also saw Selection.SpecialCells(xlFormulas, 16)
TIA,
Jim

"
wrote in message
:

Interesting Bob, did you literally mean Find()? or,
For Each MyCell in My Range ?

Thanks


"Bob Phillips" wrote:

Nope, you need to go find em.

--

Dave Peterson

How to select Formula cells with red interior?
 

Oops. I forgot to limit the replace to just the range with the formulas!

Option Explicit
Sub testme()

Dim myRng As Range

With ActiveSheet
Set myRng = Nothing
On Error Resume Next
Set myRng = .Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
End With

If myRng Is Nothing Then
MsgBox "No formulas in sheet!"
Exit Sub
End If

With Application.FindFormat
.Clear
.Interior.ColorIndex = 3
End With

With Application.ReplaceFormat
.Clear
.Interior.ColorIndex = 27
End With

myRng.Cells.Replace What:="", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=True, ReplaceFormat:=True

End Sub

Dave Peterson wrote:

With xl2002+, excel added a way to replace the formatting of cells, too. You
can see it under the Options button on the edit|Replace dialog.

In code:
Option Explicit
Sub testme()

Dim myRng As Range

With ActiveSheet
Set myRng = Nothing
On Error Resume Next
Set myRng = .Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No formulas in sheet!"
Exit Sub
End If

With Application.FindFormat
.Clear
.Interior.ColorIndex = 3
End With

With Application.ReplaceFormat
.Clear
.Interior.ColorIndex = 27
End With

.Cells.Replace What:="", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=True, ReplaceFormat:=True

End With

End Sub

In my workbook, red had a colorindex of 3 and bright yellow is 27. These may
differ for you. You can record a macro while changing the fill color to see
what your workbook uses. (Colors travel with the workbook, so it could be
different for each workbook--if someone customized the color scheme.)

wrote:

2003

Is there a way to select all formula cells with i.e. a red interior color in one selection?

I do know the following:
Set MyRange = Selection.SpecialCells(xlFormulas, 23)
But without processing all MyCells in MyRange individually, can I
select ALL formula cells with a red interior then change ALL to
yellow?

Thanks

EagleOne

--

Dave Peterson

--

Dave Peterson

How to select Formula cells with red interior?
 

Dave,

What is the purpose of the "at the end" VBA statement below? In the sequence, it seems to be after
the color change. Maybe VBA needs the aggregation of all the code statements
that you separated for clarity?

Thanks

Activesheet.Cells.Replace What:="", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=True, ReplaceFormat:=True


Dave Peterson wrote:

.Cells.Replace What:="", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=True, ReplaceFormat:=True

How to select Formula cells with red interior?
 

That is how I would find them, using a test within For Each ... Next

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
...
Interesting Bob, did you literally mean Find()? or,
For Each MyCell in My Range ?

Thanks


"Bob Phillips" wrote:

Nope, you need to go find em.

How to select Formula cells with red interior?
 

First, make sure you read that second post--with the fix.

Second, the stuff before this line essentially just set the colors in the
Edit|replace dialog. That other stuff didn't actually do the Replace|All
portion.

Third, make sure you read that second post--with the fix!

wrote:

Dave,

What is the purpose of the "at the end" VBA statement below? In the sequence, it seems to be after
the color change. Maybe VBA needs the aggregation of all the code statements
that you separated for clarity?

Thanks

Activesheet.Cells.Replace What:="", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=True, ReplaceFormat:=True

Dave Peterson wrote:

.Cells.Replace What:="", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=True, ReplaceFormat:=True

--

Dave Peterson

How to select Formula cells with red interior?
 

Thanks perterod

" wrote in message
:

Highlight "specialcells" in your code and hit F1.

Then click on "XlSpecialCellsValue" in the Value description.

You'll see:

XlSpecialCellsValue can be one of these XlSpecialCellsValue constants.
xlErrors
xlLogical
xlNumbers
xlTextValues

If you type this in the immediate window:
?xlErrors
you'll see xlErrors is a constant for 16.

Do the same for all 4 constants. Then add those numbers. You'll see the
connection.

If you record a macro when you do:
edit|goto|special|check formulas
and change some of the options, you'll see other numbers in the code. I bet
you'll see how the recorder got it.


Jim May wrote:

In the line:
Selection.SpecialCells(xlFormulas, 23)
What does the 23 indicate?
Where can I find the Numbers listed with meaning in Help?
I looked but was unable to find - even searching Google?
I also saw Selection.SpecialCells(xlFormulas, 16)
TIA,
Jim

"
wrote in message
:

Interesting Bob, did you literally mean Find()? or,
For Each MyCell in My Range ?

Thanks


"Bob Phillips" wrote:

Nope, you need to go find em.

--

Dave Peterson

How to select Formula cells with red interior?
 

Thanks Dave and Bob,

Dave,

I was about to ask and I attempted myRng.Cells.Replace What:="", Replacement:="",
and it worked fine. Guess I am beginning to slightly get this stuff!

Thanks for the insight to Application.FindFormat. I would not have found that.

EagleOne


Dave Peterson wrote:

Oops. I forgot to limit the replace to just the range with the formulas!

Option Explicit
Sub testme()

Dim myRng As Range

With ActiveSheet
Set myRng = Nothing
On Error Resume Next
Set myRng = .Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
End With

If myRng Is Nothing Then
MsgBox "No formulas in sheet!"
Exit Sub
End If

With Application.FindFormat
.Clear
.Interior.ColorIndex = 3
End With

With Application.ReplaceFormat
.Clear
.Interior.ColorIndex = 27
End With

myRng.Cells.Replace What:="", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=True, ReplaceFormat:=True

End Sub

Dave Peterson wrote:

With xl2002+, excel added a way to replace the formatting of cells, too. You
can see it under the Options button on the edit|Replace dialog.

In code:
Option Explicit
Sub testme()

Dim myRng As Range

With ActiveSheet
Set myRng = Nothing
On Error Resume Next
Set myRng = .Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No formulas in sheet!"
Exit Sub
End If

With Application.FindFormat
.Clear
.Interior.ColorIndex = 3
End With

With Application.ReplaceFormat
.Clear
.Interior.ColorIndex = 27
End With

.Cells.Replace What:="", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=True, ReplaceFormat:=True

End With

End Sub

In my workbook, red had a colorindex of 3 and bright yellow is 27. These may
differ for you. You can record a macro while changing the fill color to see
what your workbook uses. (Colors travel with the workbook, so it could be
different for each workbook--if someone customized the color scheme.)

wrote:

2003

Is there a way to select all formula cells with i.e. a red interior color in one selection?

I do know the following:
Set MyRange = Selection.SpecialCells(xlFormulas, 23)
But without processing all MyCells in MyRange individually, can I
select ALL formula cells with a red interior then change ALL to
yellow?

Thanks

EagleOne

--

Dave Peterson

How to select Formula cells with red interior?
 

Right again Dave!

Dave Peterson wrote:

First, make sure you read that second post--with the fix.

Second, the stuff before this line essentially just set the colors in the
Edit|replace dialog. That other stuff didn't actually do the Replace|All
portion.

Third, make sure you read that second post--with the fix!

wrote:

Dave,

What is the purpose of the "at the end" VBA statement below? In the sequence, it seems to be after
the color change. Maybe VBA needs the aggregation of all the code statements
that you separated for clarity?

Thanks

Activesheet.Cells.Replace What:="", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=True, ReplaceFormat:=True

Dave Peterson wrote:

.Cells.Replace What:="", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=True, ReplaceFormat:=True

How to select Formula cells with red interior?
 

Excellent information, Dave

Thanks

"David McRitchie" wrote:

The best way is to use the same method that you
colored them in the first place; however, you can use
a macro to check every cell individually (time consuming)
Counting cells based on interior or font color
http://www.cpearson.com/excel/colors.htm

For directions in installing and using a macro see
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Some additional examples using Chip Pearson's macros
Interior Color, using Count, SUM, etc. (#count)
http://www.mvps.org/dmcritchie/excel/colors.htm#count

And you also specifically asked about Special Cells
Select cells with either formulas or constants (#specialcells)
http://www.mvps.org/dmcritchie/excel...m#specialcells

Some notations on the above code, and use of Special Cells (speed
and efficiency considerations) « (#notations)
http://www.mvps.org/dmcritchie/excel....htm#notations
---
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

<donoteventry; <removes; wrote in message ...
2003

Is there a way to select all formula cells with i.e. a red interior color in one selection?

I do know the following:
Set MyRange = Selection.SpecialCells(xlFormulas, 23)
But without processing all MyCells in MyRange individually, can I
select ALL formula cells with a red interior then change ALL to
yellow?

Thanks

EagleOne