color cells with formulas

Joanna_gr

Board Regular
Joined
Feb 16, 2002
Messages
149
Hi again!

I want to apply special formatting to cells that contain formulas.
I think u've already answered this but i don't know where to locate the answer. Can u please help me?
Thanx
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
On 2002-02-26 02:38, Joanna_gr wrote:
Hi again!

I want to apply special formatting to cells that contain formulas.
I think u've already answered this but i don't know where to locate the answer. Can u please help me?
Thanx

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
 
Upvote 0
Thanx Derek, Aladin

Derek, it worked. thank u very much. Can u please explain to me what CELL(48,A1) refers to? thanx again.
 
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
 
Upvote 0
On 2002-02-26 05:00, Derek wrote:
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

Here is a link to get more info:

http://www.j-walk.com/ss/excel/usertips/tip045.htm
 
Upvote 0
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.

On 2002-02-26 05:00, Derek wrote:
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
 
Upvote 0
I noticed that too late!


On 2002-02-26 05:49, Juan Pablo G. wrote:
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.

On 2002-02-26 05:00, Derek wrote:
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
 
Upvote 0
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
 
Upvote 0
On 2002-02-26 06:29, Autolycus wrote:
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.

That's interesting. Thanks pointing out.

Aladin
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top