pivot table Vlookup to return cell colour and font

[T15MAF]

Hi,

1st post here, so a big Hi to everyone.

I am trying to return the cell colour and font format using a vlookup on the pivot table (see below snip) I can get the Vlookup to return the risk ranking number but I don't know how to get it to return the cell colour and font. I have a data validation for the impact and likelihood so when i change these values it gives me the corresponding risk rating but not the colour of the cell and the font. I know i have to use VBA for this but not sure what code to write ort even if this is possible.... Any excel boffins out there that can help me out

Thanks in advance, Mark

 

[etaf]

can you make another grid for the colour and then you can use conditional formatting
with a grid lookup - so you would have a 1,2,3 or blank for each colour

not sure it can be worked out from the grid without vba to pick up the colour - BUT there maybe a way .....

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2	imapct	likelihood	risk					1	2	3	4	5	6	7	8
3	g	3	4				a	8	9	10	11	12	13	14	15
4	g	1	2				b	7	8	9	10	11	12	13	14
5	f	6	8				c	6	7	8	9	10	11	12	13
6	b	5	11				d	5	6	7	8	9	10	11	12
7	g	6	7				e	4	5	6	7	8	9	10	11
8	b	7	13				f	3	4	5	6	7	8	9	10
9	d	8	12				g	2	3	4	5	6	7	8	9
10	e	4	7				h	1	2	3	4	5	6	7	8
11	a	8	15
12
13
14
15
16							COLOURS		likely hood
17								1	2	3	4	5	6	7	8
18							a	1	2	2	2	2	2	2	2
19							b	1	1	2	2	2	2	2	2
20							c	1	1	1	2	2	2	2	2
21							d			1	1	1	2	2	2
22							e				1	1	1	1	1
23							f					3	3	3	3
24							g						3	3	3
25							h							3	3
26
27
Sheet1

Cell Formulas
Range	Formula
C3:C11	C3	=INDEX($H$3:$O$10,MATCH($A3,$G$3:$G$10,0),MATCH($B3,$H$2:$O$2,0))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H18:O25	Expression	=H18=1	text	NO
H18:O25	Expression	=H18=2	text	NO
H18:O25	Expression	=H18=3	text	NO
A3:C11	Expression	=INDEX($H$18:$O$25,MATCH($A3,$G$18:$G$25,0),MATCH($B3,$H$17:$O$17,0))=1	text	YES
A3:C11	Expression	=INDEX($H$18:$O$25,MATCH($A3,$G$18:$G$25,0),MATCH($B3,$H$17:$O$17,0))=2	text	NO
A3:C11	Expression	=INDEX($H$18:$O$25,MATCH($A3,$G$18:$G$25,0),MATCH($B3,$H$17:$O$17,0))=3	text	NO