Highlight a cell that has a reference on another sheet

[dvaladas]

Hello,

Is there any chance to highlight a cell that is a reference of another cell?

For example, I have in Sheet1 a cell with the formula =Sheet2'!B1 (the value is 0,001). I need to highlight this value 0,001 on Sheet 2. It is possible?

The goal is know which cells of sheet 2 have a reference on sheet 1.

Thanks

 

[MickG]

One way :-

Dim sp As Variant
sp = Split([a1].Formula, "!")
Sheets(Mid(sp(0), 2)).Range(sp(1)).Interior.Color = vbYellow

 

[63falcondude]

If you have Excel 2013+, you can use the FORMULATEXT function in a Conditional Formatting formula.

If you do not have this function, you can create a UDF for it

Function FormulaText(rIn as range) as string
FormulaText = rIn.Cells(1).Formula
End Function

Once you have this function, highlight the range that you want to check (i.e. A1:D100) > Conditional Formatting > New Rule > Use a formula

=ISNUMBER(SEARCH("Sheet1",FORMULATEXT(A1)))

Format: Fill color of your choice > OK > OK

 

[dvaladas]

One way :-

Dim sp As Variant
sp = Split([a1].Formula, "!")
Sheets(Mid(sp(0), 2)).Range(sp(1)).Interior.Color = vbYellow

Thanks for the reply but I'm not understanding how can I adapt the code. The thing is I have 50 values on sheet 1 anda my sheet 2 is dynamic and at this time have 50 numbers that are linked to the 50 values of sheet 1.
But I need to know if the next time that I update the sheet 2, which cells of sheets 2 are not linked to sheet 1.

My expected output is highlight the cells of sheet 2 that are linked to the cells of sheet 1. Don't need to be vba, can be a formula too on conditional formatting (if possible).

 

[Rick Rothstein]

Thanks for the reply but I'm not understanding how can I adapt the code. The thing is I have 50 values on sheet 1 anda my sheet 2 is dynamic and at this time have 50 numbers that are linked to the 50 values of sheet 1.
But I need to know if the next time that I update the sheet 2, which cells of sheets 2 are not linked to sheet 1.

With Sheet2 active, are you looking for formulas that reference cell only on single specified sheet such as Sheet1? Or are you more generally looking for formulas that reference cells on any of the other sheets in the workbook no matter what sheets those are?

 

[dvaladas]

With Sheet2 active, are you looking for formulas that reference cell only on single specified sheet such as Sheet1? Or are you more generally looking for formulas that reference cells on any of the other sheets in the workbook no matter what sheets those are?

Hey Rick,

I just found your code on the thread "VBA to format cell if cell has dependents" and it is perfect to what I nedd!

Thanks!!

 

[dvaladas]

Tank you all guys, it is solved!

 

[Rick Rothstein]

Hey Rick,

I just found your code on the thread "VBA to format cell if cell has dependents" and it is perfect to what I nedd!

Great! That is why I asked the question I did, because I thought that code might be what you wanted. For anyone else following this thread who might be interested in the solution the OP found, here is the link to it...

https://www.mrexcel.com/forum/excel...-cell-if-cell-has-dependents.html#post4278191