Is there a way of highlighting cells lower or higher containing text and numbers.

[Davebro]

Is there anyway of highlighting cells lower or higher. Green for lower and red for higher. I am using a pivot table where A1 changes.
I could just delete the word class, in each of the columns I suppose, but this is extra work and does not look as good.

Class 4
A		Class 1
B		Class 2
C		Class 3
D		Class 4
E		Class 5
F		Class 6
G		Class 4`
H		Class 2

 

[etaf]

you could use this in conditional formatting

will there be a ' in the text as in G
also will the class be more than 1 number

are you still using 2016 excel

showing the formula

Book7
	A	B	C	D	E
1	Class 4
2
3	A		Class 1		TRUE
4	B		Class 2		TRUE
5	C		Class 3		TRUE
6	D		Class 4		FALSE
7	E		Class 5		FALSE
8	F		Class 6		FALSE
9	G		Class 4		FALSE
10	H		Class 2		TRUE
Sheet1

Cell Formulas
Range	Formula
E3:E10	E3	=(RIGHT(C3,1)*1)<RIGHT($A$1,1)*1

with conditional formatting

Book7
	A	B	C
1	Class 4
2
3	A		Class 1
4	B		Class 2
5	C		Class 3
6	D		Class 4
7	E		Class 5
8	F		Class 6
9	G		Class 4
10	H		Class 2
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C3:C10	Expression	=(RIGHT(C3,1)*1)>RIGHT($A$1,1)*1	text	NO
C3:C10	Expression	=(RIGHT(C3,1)*1)<RIGHT($A$1,1)*1	text	NO

 

[Davebro]

Hello, many thanks for your solution, I am still using 2016 version. This works fine, thank you so much for your help.

 

[etaf]

you are welcome