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

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

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?

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

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.