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.

 

[etaf]

select C1 to C3
then in a conditional format formula

and you could use a formula
D1 = whatever has the 14,15,16

But if someone changes the formula to GBP 14 (cell D2) or USD 15 (cell D3)

What cell is changed to 14,15,16
Not 100% following

 

[MartinL]

Hi @etaf

Cell D8 has the formula =VLOOKUP(B,'Pricing'!$A:$R,D$1,0)
So currently the cell I want highlighted references D1 which has the Value 14 which corresponds to EURO.
Therefore I would like either EURO or 14 or both to be highlighted

However if someone changes the formula to =VLOOKUP($B7,'Pricing'!$A:$R,D$2,0)
Then the cell I want either GBP or 15 or both to be highlighted

IF the formula =VLOOKUP($B7,'Pricing'!$A:$R,D$3,0)
Then the cell I want either USD or 16 or both to be highlighted

 

[Marcelo Branco]

Cell D8 has the formula =VLOOKUP(B,'Pricing'!$A:$R,D$1,0)

Is the formula in D8 or in D7?

If in D8 it should be
=VLOOKUP(B8,'Pricing'!$A:$R,D$1,0)

Please, clarify.

M.

 

[Marcelo Branco]

Assuming the formula below in D8
=VLOOKUP(B8,'Pricing'!$A:$R,D1,0)

Select C1:D3
In Conditional Formatting use this formula
=VLOOKUP($B$8,Pricing!$A:$R,$D1,0)=$D$8
pick a format (e.g Font Color = Red)

M.

 

[MartinL]

Assuming the formula below in D8
=VLOOKUP(B8,'Pricing'!$A:$R,D1,0)

Select C1:D3
In Conditional Formatting use this formula
=VLOOKUP($B$8,Pricing!$A:$R,$D1,0)=$D$8
pick a format (e.g Font Color = Red)

M.

Thank wont work as =VLOOKUP($B$8,Pricing!$A:$R,$D1,0)=a price ie 60,81,
I need to return the value in row 1 which is basically the column number 14/15/16 ect.
Row 1 in Pricing A:R is just a helper row to find the correct price

So if the formula can be reverse engineered to find the value in Row 1 then that would work, but obviously you can't do a search on the price as you would get a many to many issue.

 

[Marcelo Branco]

It worked for me

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	=PROCV($B$8;Pricing!$A:$R;$D1;0)=$D$8	text	NO

CF formula in English
=VLOOKUP($B$8,Pricing!$A:$R,$D1,0)=$D$8

Then...
Using D3 in the formula in D8 (for testing purposes i inserted a value in column 16 of the spreadsheet Pricing = 128,77)
=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	=PROCV($B$8;Pricing!$A:$R;$D1;0)=$D$8	text	NO

M.

 

[etaf]

Thank wont work as =VLOOKUP($B$8,Pricing!$A:$R,$D1,0)=a price ie 60,81,
I need to return the value in row 1 which is basically the column number 14/15/16 ect.

So R = column 18
therefore if
D1 = 14 , thats using column N
D1 = 15 , thats using column O
D1 = 16 , thats using column P
to lookup
I have used an INDIRECT & MID in a formula

so mocked up a table
The formula is Whereever you have the formula
=VLOOKUP($B7,'Pricing'!$A:$R,D$3,0)
I have got in the example THAT formula is in A7 on a sheet - so you need to change the $A$7 to whatever cell the formula is in

=INDIRECT(MID(FORMULATEXT($A$7),LEN(FORMULATEXT($A$7))-5,3))

Then select D1 to D3
and use a formula in conditional formatting
where
D1=INDIRECT(MID(FORMULATEXT($A$7),LEN(FORMULATEXT($A$7))-5,3))
Now as the cell in the formula changes from using D1,D2,D3 - which = 14,15,16
that number will be picked up and used in
=INDIRECT(MID(FORMULATEXT($A$7),LEN(FORMULATEXT($A$7))-5,3))
and for the conditional formatting formula we can use
D1=INDIRECT(MID(FORMULATEXT($A$7),LEN(FORMULATEXT($A$7))-5,3))

Book4
	A	B	C	D
1	a	c1-1	c2-1	12
2	b	c1-2	c2-2	13
3	c	c1-3	c2-3	14
4
5
6	Vlookup Formula	Example of output - just to show results
7	0	D$2	Extract what cell is used in formula
8		13	Use the contents of the cell in the formula
Sheet2

Cell Formulas
Range	Formula
A7	A7	=VLOOKUP($B4,$A:$R,D$2,0)
B7	B7	=MID(FORMULATEXT(A7),LEN(FORMULATEXT(A7))-5,3)
B8	B8	=INDIRECT(MID(FORMULATEXT(A7),LEN(FORMULATEXT(A7))-5,3))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D1:D3	Expression	=D1=INDIRECT(MID(FORMULATEXT($A$7),LEN(FORMULATEXT($A$7))-5,3))*1	text	NO

 

[MartinL]

Hi @Marcelo Branco / @etaf
Thank you both for your assistance.

@Marcelo Branco : I'm obviously doing something wrong as I cannot get your solution to work at all
@etaf : interesting ly I could not get yours to work as you laid it out, However I did use part of your solution to get my result
I used the formula =INDIRECT(MID(FORMULATEXT($A$7),LEN(FORMULATEXT($A$7))-5,3)) to return the column number from the lookup (that was great, many thanks)
and put this in cell A1 which I then greyed out
I then simply did the following conditional formats
Over C1 [A1 = 14] Fill Yellow text Bold
Over C2 [A1 = 15] Fill Yellow text Bold
Over C3 [A1 = 16] Fill Yellow text Bold
Over C4 [A1 = 17] Fill Yellow text Bold

I'll look into why your solutions didn't work as laid out

 

[etaf]

$A$7 , as mentioned needs to be where your vlookup formula is situated
and the number of characters in the formula need to be the same as the example you gave , or at least the last bit does
D$3,0)
=VLOOKUP($B7,'Pricing'!$A:$R,D$3,0)
Since i'm changing the formula to text , extracting the D$3 part from the formula using MID
so the start number in the MID() is the Len() of the text - 5 and so
that means it starts the MID() at the D and then takes 3 characters D$3

The indirect takes the contents from the cell
and so will return , 14 or 15 or 16 or 16

The conditional formatting
now looks to see if the range you select in conditional formatting = then indirect number and if it does it highlights the cell

Not sure from your description how you used the workaround - not following
But glad you have a solution