Change linked cell color if value changes

[jknowlden]

Basically, I have a workbook with several sheets. Sheet 1 is a "master" sheet and it contains a ton of numeric and text values. The other sheets reference cells from this "master" sheet. This way, a change on the master sheet is reflected in all the other sheets.

What I want: When a value on the "master" sheet changes, all the cells (on the other sheets) that are linked to the changed cell (on the "master" sheet) will change background or font color, border, etc. This would be a visual flag that lets me know something has changed when I am looking at sheets other than the "master" sheet.

I'm familiar with some programming (MatLab and C) but not VBA. I found the below code on some forums, but it changes the color of a cell when a change is made, but the color change does not propagate to the various cells that are linked to the original (changed) cell. Any help would be greatly appreciated....

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = 8
End Sub

 

[MickG]

Try this:-
Run Code From sheet "Master"
It will pickup on any formula that has The Word "Master" in the formula and whose formula refers Directly to the Same cell Address as the Target address

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] sht [COLOR="Navy"]As[/COLOR] Worksheet
[COLOR="Navy"]Dim[/COLOR] cell [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]If[/COLOR] Target.Count = 1 [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] sht [COLOR="Navy"]In[/COLOR] Worksheets
    [COLOR="Navy"]If[/COLOR] Not sht.Name = "Master" [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] cell [COLOR="Navy"]In[/COLOR] sht.UsedRange
        [COLOR="Navy"]If[/COLOR] cell.HasFormula [COLOR="Navy"]Then[/COLOR]
            cell.Interior.ColorIndex = xlNone
                [COLOR="Navy"]If[/COLOR] InStr(cell.Formula, Target.Address(0, 0)) And InStr(cell.Formula, ActiveSheet.Name) [COLOR="Navy"]Then[/COLOR]
                cell.Interior.ColorIndex = 6
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] cell
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] sht
MsgBox "run !!"
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[MickG]

Hopefully this takes care of "Absolute & Relative " Cell references.

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] sht [COLOR="Navy"]As[/COLOR] Worksheet
[COLOR="Navy"]Dim[/COLOR] cell [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] RelForm [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Pos [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]If[/COLOR] Target.Count = 1 [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] sht [COLOR="Navy"]In[/COLOR] Worksheets
    [COLOR="Navy"]If[/COLOR] Not sht.Name = "Master" [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] cell [COLOR="Navy"]In[/COLOR] sht.UsedRange
        [COLOR="Navy"]If[/COLOR] cell.HasFormula [COLOR="Navy"]Then[/COLOR]
            cell.Interior.ColorIndex = xlNone
            RelForm = Replace(cell.Formula, "$", "")
            Pos = InStr(RelForm, Target.Address(0, 0))
               [COLOR="Navy"]If[/COLOR] Pos > 0 And Not IsNumeric(Mid(RelForm, Pos + Len(Target.Address(0, 0)), 1)) And InStr(cell.Formula, ActiveSheet.Name) [COLOR="Navy"]Then[/COLOR]
                    cell.Interior.ColorIndex = 6
               [COLOR="Navy"]End[/COLOR] If
       [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] cell
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] sht
MsgBox "run !!"
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Cease]

I have next to no knowledge of coding, you might already have your answer above, but without coding in excel 2007/2010 you can conditional format based on a formula, and in that you can have it based on values on another page.

for instance if you're on Sheet2 and want cell b3 to be blue if cell b3 on the master sheet is greater than 0 you'd use:
select the cell, hit up conditional formatting and choose new rule: "Use formula to determine which cells to format"
and this in the field:
=if(Master!b3">0",true,false)
and then set the format to look how you'd want.

No idea if this conditional formatting is in any earlier version, but this helped me a ton.

 

[jknowlden]

I just gave it a quick try and it seems to be just what I'm looking for. I appreciate the help. On a side note, any suggestions on good texts or websites to start learning VBA? I'm familiar will c code but was interested in learning visual basic…<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[jknowlden]

And CEASE, the conditional formatting seems to work well too, but the equation is based on the cell be greater than zero... What I need is for the formatting to change if the original cell is changed at all, regardless of whether it is greater than zero, less than zero, or it could even be text that changed... Any ideas?

 

[Cease]

Hey,
I had to play around a little bit, but you can put in:

=IF(NOT(C10=""),TRUE,FALSE)

worked for me and I learned something with this too :D

There's probably a better way to do it, but this makes sense to me. Hope it helps :D