Macro to Color Cell Font When Linking to Other Cells in Same Sheet

[mp1021257]

Hi,

I am trying to write a simple macro that will color a cell's font green if a cell links to another cell in the same worksheet. (I have come across macros online that will color a cell's font if it links to another cell in a separate worksheet or workbook, but unfortunately, nothing that solves my problem.

Simply put, I would like a macro that will evaluate if, for example, cell C2 is set equal to cell A2. If true, I would like the font of cell C2 to be green. Any easy way to do this?

Thank you for your help!

 

[RickXL]

Hi,

This might get you started:

Sub ColorDependents()
    Dim r As Range
    For Each r In ActiveSheet.UsedRange.Dependents
        r.Font.ColorIndex = 4
    Next
End Sub

Regards,

 

[mp1021257]

Thanks, Rick. I appreciate the quick response.

Unfortunately, this gets me the same problem I was running into on my own. This colors formulas green as well. Thoughts on how to to make this apply only to cells that link to other cells, but do not have a formula in them?

I would like this to apply only to simple cases where I am setting on cell equal to another (e.g. C2 is set equal to A2, but not where C2 =SUM(A2,B2)).

 

[RickXL]

C2 is set equal to A2

How are you setting C2 equal to A2 without inserting a formula?

Thanks,

 

[mp1021257]

Would it be possible to add a constraint here that only colors the dependent green if the text of the formula matches an equals sign + the address of the dependent cell?

In other words, using my same example as before, if the following conditions are met: (1) cell A2 is a dependent of cell C2; and (2) the text of the formula in cell C2 is precisely "=A2" (or more generally "="&"[R1C1 address of variable dependent cell]", then color font green. If not, (i.e. text of the formula in cell C2 is something like "=SUM(A2)", then do not color the font.

 

[mp1021257]

I am using a formula. I am saying C2 =A2.

 

[mp1021257]

I want to color green only in situations where I am directly linking (i.e. C2 =A2). Not in any other situations (e.g. C2 =SUM(A2)).

Thanks.

 

[mp1021257]

I am trying to distinguish between simple links to other cells on the same sheet and more complex formulas.

 

[RickXL]

You could do this:

Sub ColorDependents()
    Dim r As Range
    Dim s As Range
    
    For Each r In ActiveSheet.UsedRange.Dependents
        On Error Resume Next
        Set s = Range(Mid(r.Formula, 2))
        If Err.Number = 0 Then r.Font.ColorIndex = 4
    Next
End Sub

That colours all the cells with dependants but skips the ones that do not contain just a single cell reference.

If tries to set Range s to the Address. If it fails it assumes that the cell contents are therefore not to be coloured green.


Regards,

 

[mp1021257]

Works perfectly. Thanks!