Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
When creating a formula how do you select only visible cells | Excel Discussion (Misc queries) | |||
dynamically select a range of cells for use in a formula | Excel Worksheet Functions | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
what allows me to select cells for use in a formula by using a mo. | Excel Worksheet Functions |