Formulas with Letters and Numbers

[TJones85]

I want to create a formula on my spreadsheet. There is a scale of values, these are : BKL, 0.25, 0.5, 0.75, K, K.25, K.5, K.75, 1, 1.25 etc.
I want the colour of the cell in the second column to change depending on how far away it is in value from the value in the first column.
So e.g. BKL in the first column to K.25 has increased by 5 points and I would like the colour to show green (in an ideal world I'd love the depth of colour in the cell to get greater as the difference in value increases but will settle for just green at this stage). Similarly I'd like where the value has decreased the second column shows red. I can't work out how to do this when my values aren't simply numeric. Can anyone help?

 

[bobsan42]

I am not 100% certain what is the exact result you are after. What do you mean by "the second column"?
I would assign a number value to each scale and then use the number to apply Conditional formatting.
The only thing that does not show in the XLBB code below is that the numbers in column AB have a custom number format set to SPACE character.

Book1
	T	U	V	W	X	Y	Z	AA	AB	AC
20
21		SCALE			DATA1			DATA2
22		BKL	0		0.25	1		0.25
23		0.25	1		0.5	2		0.5
24		0.5	2		K	4		K
25		0.75	3		k.75	7		k.75
26		K	4		0.75	3		0.75
27		K.25	5		1	8		1
28		K.5	6		1.25	9		1.25
29		K.75	7		k.5	6		k.5
30		1	8
31		1.25	9
32
Sheet2

Cell Formulas
Range	Formula
V23:V31	V23	=V22+1
Y22:Y31,AB22:AB31	Y22	=IFERROR(VLOOKUP(""&X22,$U$22:$V$31,2,0),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AB22:AB31	Cell Value	<$Q21	text	NO
AB22:AB31	Other Type	Color scale		NO
Y22:Y31	Cell Value	<$Q21	text	NO
Y22:Y31	Other Type	Color scale		NO
V22:V31	Other Type	Color scale		NO