Archive of Mr Excel Message Board


Back to Dates in Excel archive index
Back to archive home

coloring cells based upon formula reference

Posted by George on February 20, 2001 11:54 AM
I have a workbook with 12 different sheets.

Some of the formulas reference cells on the same
sheet while other formulas reference cells on other
sheets.

Is there a way to change the cell color to red if the
formula in that cell refers to another sheet?

For Example:
in sheet 1, the formula in cell c1 is a1+b1. (this
cell color is okay)

in sheet 2, the formula in cell c1 is Sheet1!C1+a1+b1
(I want this cell to be red)

thanks


Re: coloring cells based upon formula reference

Posted by David Hawley on February 21, 2001 3:21 AM

Hi George

It is possible but only with VBA.

Right click on your sheet name tab and select "View Code", paste this code over what you see.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.HasFormula = True Then
If Target.Formula Like ("=*!*") Then
Target.Interior.ColorIndex = 3
End If
End If

End Sub

Push Alt+Q and save.

Dave

OzGrid Business Applications


Re: coloring cells based upon formula reference

Posted by George on February 21, 2001 11:16 AM

Thanks, but I am very new to this. How do I get it to run?


Re: coloring cells based upon formula reference

Posted by David Hawley on February 21, 2001 9:26 PM
George, it will run automatically each time you enter a formula that refrences another sheet.

Your formuals that already have other sheets in their refrence will need to be re-entered to change colour.

You should be able to achieve this by Usung Edit>Replace and replace all: = with =
This should force your formula cells to be re-entered

Dave


OzGrid Business Applications


Re: coloring cells based upon formula reference

Posted by George on February 22, 2001 11:13 AM


Thanks a bunch. This saved a whole lotta headaches.

I even changed the color myself in the script.
(a big step for a beginner).

Now maybe a tougher question.
Can I color the cells which will get used in a
formula on another sheet?

Sort of color coding the auditing tools.
Red is if a formula references another sheet (like you already helped with)
Blue is if a cell gets used on another sheet
Green if both.

Is this possible or am I trying to bite off more
than I can chew?

Thanks again for the original help.


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.