Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,593
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 391
Default 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.

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 430
Default 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.


  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22
Default 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



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 903
Default 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




  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 391
Default 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



  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,593
Default 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.



  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 430
Default 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


  #14   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 391
Default 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

  #15   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 391
Default 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



  #16   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 391
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
When creating a formula how do you select only visible cells fuadramsey Excel Discussion (Misc queries) 1 June 13th 06 01:35 AM
dynamically select a range of cells for use in a formula isofuncurves Excel Worksheet Functions 1 February 2nd 06 02:34 AM
checking that cells have a value before the workbook will close kcdonaldson Excel Worksheet Functions 8 December 5th 05 05:57 PM
Match then lookup Tenacity Excel Worksheet Functions 9 December 3rd 05 06:30 AM
what allows me to select cells for use in a formula by using a mo. Gemini Excel Worksheet Functions 1 January 24th 05 03:52 AM


All times are GMT +1. The time now is 09:33 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"