Format a cell if used in a formula

[MartinL]

I have a range of cells containing currency conversion values and their corresponding currencies
I then have a table which points to the selected currency factor.
I'd like to highlight the currency cell used in the table - if that's possible.
Cells C1:D3

EURO	14
GBP	15
USD	16

Table1 Cells B7:D8

SKU	QTY	Price
1000000123	1	123.99

Price contains the formula
VLOOKUP($B7,'Pricing'!$A:$R,D$1,0)

In this instance I'd like Cell C1 to be highlighted so that it can be seen that EURO's are being used
But if someone changes the formula to GBP 14 (cell D2) or USD 15 (cell D3)
Then I'd like C2 or C2 to be highlighted
As a workaround D1, D2 or D3 could be highlighted instead.

 

[Marcelo Branco]

@Marcelo Branco : I'm obviously doing something wrong as I cannot get your solution to work at all

Martin

Lets try again

You didn't answer my questions. Please, clarify
Is the formula, that gets the price from the sheet Pricing, in D8?
If so, is it exactly?
=VLOOKUP(B8,Pricing!$A:$R,D1,0)

M.

 

[Marcelo Branco]

Martin,

Looks like you already have a solution, so please disregard my post above.

Either I didn't understand your problem correctly or something strange is happening while implementing my solution in your spreadsheet.

Merry Christmas

M.

 

[etaf]

Marcelo Branco​

May have a better solution, as mine is volatile with indirect() which every time you do anything on the spreadsheet it will recalculate

my solution wont work if its
=VLOOKUP(B8,Pricing!$A:$R,D1,0)
and NOT
=VLOOKUP(B8,Pricing!$A:$R,D$1,0)
Note you had a $ in your original post
so the -5
gets back to the D
and extract 3 characters D$1
if its ONLY
D1 and not D$1
then its back -4 and Only extract 2
=INDIRECT(MID(FORMULATEXT($A$7),LEN(FORMULATEXT($A$7))-4,2))

 

[Marcelo Branco]

@Marcelo Branco​

May have a better solution, as mine is volatile with indirect() which every time you do anything on the spreadsheet it will recalculate

Hi Etaf

Have you tried my solution (suggestion)? It worked?

M.

 

[etaf]

Have you tried my solution (suggestion)? It worked?

no i didn't try , but sure it was ok,
I can have a look now

 

[Marcelo Branco]

Another possible solution without volatile functions

Pricing sheet

Pasta1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	SKU	Col2	Col3	Col4	Col5	Col6	Col7	Col8	Col9	Col10	Col11	Col12	Col13	EURO	GPB	USD	Col17	Col18
2	1000000123													123,99	99,78	128,77
3
Pricing

Sheet of interest
Select C1:D3

Formula used in CF in English
=MATCH($D$8,INDEX(Pricing!$A:$R,MATCH($B$8,Pricing!$A:$A,0),0),0)=$D1

Scenario 1
Formula in D8
=VLOOKUP(B8,Pricing!$A:$R,D1,0)

Pasta1
	B	C	D
1		EURO	14
2		GBP	15
3		USD	16
4
5
6
7	SKU	QTY	Price
8	1000000123	1	123,99
Plan1

Cell Formulas
Range	Formula
D8	D8	=VLOOKUP(B8,Pricing!$A:$R,D1,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1:D3	Expression	=CORRESP($D$8;ÍNDICE(Pricing!$A:$R;CORRESP($B$8;Pricing!$A:$A;0);0);0)=$D1	text	NO

Scenario 2
Formula in D8
=VLOOKUP(B8,Pricing!$A:$R,D2,0)

Pasta1
	B	C	D
1		EURO	14
2		GBP	15
3		USD	16
4
5
6
7	SKU	QTY	Price
8	1000000123	1	99,78
Plan1

Cell Formulas
Range	Formula
D8	D8	=VLOOKUP(B8,Pricing!$A:$R,D2,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1:D3	Expression	=CORRESP($D$8;ÍNDICE(Pricing!$A:$R;CORRESP($B$8;Pricing!$A:$A;0);0);0)=$D1	text	NO

Scenario 3
Formula in D8
=VLOOKUP(B8,Pricing!$A:$R,D3,0)

Pasta1
	B	C	D
1		EURO	14
2		GBP	15
3		USD	16
4
5
6
7	SKU	QTY	Price
8	1000000123	1	128,77
Plan1

Cell Formulas
Range	Formula
D8	D8	=VLOOKUP(B8,Pricing!$A:$R,D3,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1:D3	Expression	=CORRESP($D$8;ÍNDICE(Pricing!$A:$R;CORRESP($B$8;Pricing!$A:$A;0);0);0)=$D1	text	NO

M.