How to select Formula cells with red interior?

[donoteventry;removes;pam]

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

 

[Bob Phillips]

Nope, you need to go find em.

--
HTH

Bob Phillips

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

 

[EagleOne]

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

Thanks

 

[Jim May]

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

 

[Guest]

You might try doing this via Word.

 

[Dave Peterson]

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

 

[David McRitchie]

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/formula.htm#formula.htm#specialcells

Some notations on the above code, and use of Special Cells (speed
and efficiency considerations) « (#notations)
http://www.mvps.org/dmcritchie/excel/proper.htm#notations

 

[Dave Peterson]

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.

 

[Dave Peterson]

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

 

[EagleOne]

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

 

[Bob Phillips]

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)

 

[Dave Peterson]

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!

 

[Jim May]

Thanks perterod

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.

 

[EagleOne]

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

 

[EagleOne]

Right again Dave!

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!

 

[EagleOne]

Excellent information, Dave

Thanks

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/formula.htm#formula.htm#specialcells

Some notations on the above code, and use of Special Cells (speed
and efficiency considerations) « (#notations)
http://www.mvps.org/dmcritchie/excel/proper.htm#notations