format cells depending on formula

[Corticus]

Hi all,

Can I conditionally format cells depending on the formula contained.<elaboration>
I have a sheet that consists of nothing but references.
No data input.
I would like to highlight the cells that refer to sheets other than the one I'm looking at.
That way, I can make the sheet "stand-alone" in that the other sheets will no longer be required for data entry.
All references to other sheets can be replaced with blank cells.</elaboration>

I know the Ctrl~ thing, but its a BIG sheet, and conditional formatting (or VBA, I guess) would really help speed up the process, as well as make for interesting analysis of future sheets that need similar work.

Fruitful and Abundant Thanks!
Corticus
This message was edited by Corticus on 2002-08-23 06:39

 

[Jim North]

Corticus,

I was unable to find anyway to do it without VBA. VBA has a HASFORMULA function that you could use to change the color of the cell:


HasFormula Property Example

This example prompts the user to select a range on Sheet1. If every cell in the selected range contains a formula, the example displays a message.

Worksheets("Sheet1").Activate
Set rr = Application.InputBox( _
prompt:="Select a range on this worksheet", _
Type:=8)
If rr.HasFormula = True Then
MsgBox "Every cell in the selection contains a formula"
End If

 

[AChimpNamedCornelius]

If you delete (from a copy) the other sheets, then the cells referencing that sheet should show "#REF!" (just tried a simple experiment.)

It won't automatically blank those cells, but you should at least see all the cells quickly.

 

[Corticus]

Thanks for the help!

Chimp: It was the #Ref that got me to posting this initially

I'm in the process of revising some severly overcomplicated unit-cost-methodolgy(not that you cared), and in the process, I've been deleteing pages of data-entry, thus the #Ref.

I was hoping for a way to identify the cells that would give me #Ref so I could fix them before they went batty.

I figured, if the formula says Sum('Sheet1'!A1:A20) that I could find all the formulas with 'Sheet1', and fix em.

I would do find and replace, but its not a consitent replace.

Anyways, sorry to ramble, thanks for the help!

Peace and Blessings,
Corticus

 

[Corticus]

I just wanted to add to this post that I found an add-in on Bill Manville's sight:

http://www.bmsltd.co.uk/MVP/Default.htm

called FindLink.Zip

that appears will allow to do that which I requested in this post.

Thanks for the help,
Corticus