Conditional Formatting: Formula Reference

[baudiasuev]

Hello,

I have somewhat of a unique conditional formatting request and I'm not even sure that this can be done but thought I'd ask anyways. I am looking for a way to highlight cells that are referenced in a formula somewhere else.

For example, Sheet1 has 10 formulas in which the data is coming from Sheet2. In order to make sure that all the numbers from Sheet2 are being placed somewhere in Sheet1, I would want them to turn green if Excel detects that that exact cell reference was used to make a calculation.

Please let me know if you know a way or want me to explain in more detail!

Thank you!!

 

[kevin9999]

This won't be the final solution, I'm sure, but it may hopefully be a step in the right direction? It even seems counterintuitive to me in the result of the IF() function... You can hide the column with the FORMULATEXT() if you want.

Sheet1

Book1
	A	B
1	2
2
3	6
4	8
5
6
7
8	16
9	18
10	20
Sheet1

Cell Formulas
Range	Formula
A1,A8:A10,A3:A4	A1	=Sheet2!A1*2

Sheet2

Book1
	A	B
1	1	TRUE
2	2
3	3	TRUE
4	4	TRUE
5	5
6	6
7	7
8	8	TRUE
9	9	TRUE
10	10	TRUE
Sheet2

Cell Formulas
Range	Formula
B1:B10	B1	=IFERROR(IF(FORMULATEXT(Sheet1!$A$1:$A$10)=CELL("address",A1),FALSE,TRUE),"")
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A10	Expression	=$B1=TRUE	text	NO

 

[Peter_SSs]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:
Conditional Formatting: Highlight when referenced
Conditional Formatting: Highlight when referenced

If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.