-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
color cells with formulas
- Thread starter Joanna_gr
- Start date
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
Joanna,
You need a user-defined function which can test whether a cell houses a formula:
VBA is not my PL (neither COBOL, by the way), but you can insert the following code in a module in your WB:
Function HoldsFormula(Cell) As Boolean
HoldsFormula = Cell.Range("A1").HasFormula
End Function
Now you can apply Cond Format to, B1:B10, a range of interest:
Select B1:B10;
Activate Format|Conditional Formatting;
Choose "Formula Is" for Condition 1;
Enter in the formula box:
=HoldsFormula(B1) [ important: don't freeze B1 by using F4 -- that is, no $$ here ]
Activate Format;
Choose a color on the Patterns tab;
Click OK, OK.
Aladin
Upvote
0
Someone did post a clever answer to this some time ago. I have forgotten the author so I am sorry I can't give credit. As I remember it, it went:
Select all cells
At the toolbar select Insert,Name, Define
In "Names in Workbook" type "formula" (do NOT use quotation marks)
In "Refers to.." type GET.CELL(48,A1)
with all cells still selected go to Format, Conditional Formatting
Change "Cell value is" to "Formula is" and type in = "formula" (do NOT use quotation marks)
Select a coloured pattern and click OK
This should highlight all cells with formulas
Check conditional formattting again to ensure there are no quote marks around the word formula that you typed in.
regards
Derek
Select all cells
At the toolbar select Insert,Name, Define
In "Names in Workbook" type "formula" (do NOT use quotation marks)
In "Refers to.." type GET.CELL(48,A1)
with all cells still selected go to Format, Conditional Formatting
Change "Cell value is" to "Formula is" and type in = "formula" (do NOT use quotation marks)
Select a coloured pattern and click OK
This should highlight all cells with formulas
Check conditional formattting again to ensure there are no quote marks around the word formula that you typed in.
regards
Derek
Upvote
0
Hi Joanna
I must be honest I haven't the faintest idea what =GET.CELL(48,A1) really means. It was posted sometime ago by one of the gurus, and I'm sure if he/she reads this they will be happy to tell us. I tried different numbers from 48 to see if other magic things happened, but no, it seems 48 has some kind of special significance. Guys here seem willing to share all sorts of amazing stuff and I have received lots of help (I wish I could pretend I understand it all)
Good Luck
Derek
I must be honest I haven't the faintest idea what =GET.CELL(48,A1) really means. It was posted sometime ago by one of the gurus, and I'm sure if he/she reads this they will be happy to tell us. I tried different numbers from 48 to see if other magic things happened, but no, it seems 48 has some kind of special significance. Guys here seem willing to share all sorts of amazing stuff and I have received lots of help (I wish I could pretend I understand it all)
Good Luck
Derek
Upvote
0
Juan Pablo González
MrExcel MVP
- Joined
- Feb 8, 2002
- Messages
- 11,959
Derek, thanks... this was me. Posted as a Tip of the Day, for whoever needs it again.
This uses Excel 4 macros (Get.Cell is a "command"), and the 48 is just a parameter... there are a lot of parameters for cells (60 or 70 if i'm correct), and each one has it's own meaning !
http://www.mrexcel.com/weblog/archive/2001_12_01_webloga.shtml
Tip for Tuesday, Dec 18th. 2001.
This uses Excel 4 macros (Get.Cell is a "command"), and the 48 is just a parameter... there are a lot of parameters for cells (60 or 70 if i'm correct), and each one has it's own meaning !
http://www.mrexcel.com/weblog/archive/2001_12_01_webloga.shtml
Tip for Tuesday, Dec 18th. 2001.
Upvote
0
L
Legacy 344
Guest
I would not recommend using this method of identifying cells with formulas.
Cells that have this conditional formatting cannot be copied and pasted to other worksheets and attempting to do so might cause Excel to crash.
I use these macros to identify formulas and to remove the identification :-
Sub IdentifyFormulae_Add()
Dim ws As Worksheet
For Each ws In ActiveWindow.SelectedSheets
With ws.UsedRange
'Remove cell highlights (in case there _
are any cells highlighted but without _
formulae)
.Interior.ColorIndex = 0
'Identify Formulae
On Error GoTo e 'Error handler if there are no formula cells
.SpecialCells(xlFormulas).Interior.ColorIndex = 20
End With
Range("A1").Select
Next
Exit Sub
e: MsgBox "There are no cells with formulas"
End Sub
Sub IdentifyFormulae_Remove()
'Remove Identify Formulae (removes all _
cell highlights in the selection)
Selection.Interior.ColorIndex = 0
Range("A1").Select
End Sub
Cells that have this conditional formatting cannot be copied and pasted to other worksheets and attempting to do so might cause Excel to crash.
I use these macros to identify formulas and to remove the identification :-
Sub IdentifyFormulae_Add()
Dim ws As Worksheet
For Each ws In ActiveWindow.SelectedSheets
With ws.UsedRange
'Remove cell highlights (in case there _
are any cells highlighted but without _
formulae)
.Interior.ColorIndex = 0
'Identify Formulae
On Error GoTo e 'Error handler if there are no formula cells
.SpecialCells(xlFormulas).Interior.ColorIndex = 20
End With
Range("A1").Select
Next
Exit Sub
e: MsgBox "There are no cells with formulas"
End Sub
Sub IdentifyFormulae_Remove()
'Remove Identify Formulae (removes all _
cell highlights in the selection)
Selection.Interior.ColorIndex = 0
Range("A1").Select
End Sub
Upvote
0
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
That's interesting. Thanks pointing out.
Aladin
Upvote
0
