Help a beginner please! Conditional formatting based on value of another cell

[Carl_London]

Here is what I am trying to achieve but failing!

Basically, to change what appears in cell A2 based on the value of cell A1
If A1 is less than 10 then A2 is blank, if the value of A1 is between 10 and 29 then A2 would read BRONZE and coloured as shown in C3, and so on.
I am sure it's an easy job for an Excel ninja but I am struggling!

Thanks in advance for any help! Carl

 

[caabyyc]

To show the wording of color in B2
B2=LOOKUP(A1,{0,10,30,70},{"","BRONZE","SILVER","GOLD"})

To show different color by conditional formatting
1. Select A2
2. conditional formatting --> formula
=and(a1>=10,a1<=29)
3. choose color (BRONZE), exit to save
4. repeat steps 1-23 for other color
(SILVER)=and(a1>=30,a1<=69)
(GOLD)=a1>69

 

[Carl_London]

To show the wording of color in B2
B2=LOOKUP(A1,{0,10,30,70},{"","BRONZE","SILVER","GOLD"})

To show different color by conditional formatting
1. Select A2
2. conditional formatting --> formula
=and(a1>=10,a1<=29)
3. choose color (BRONZE), exit to save
4. repeat steps 1-23 for other color
(SILVER)=and(a1>=30,a1<=69)
(GOLD)=a1>69

Perfect, thanks!

I am enjoying learning Excel basics.

Have a great day. Best wishes, Carl

 

[awoohaw]

look at this:

Mr excel questions 66.xlsm
	A	B	C	D	E
1	45		BRONZE	FALSE	=A1>=70
2	5		SILVER	TRUE	=AND(A1<70,A1>=30)
3	10		GOLD	FALSE	=AND(A2<30,A2>=10)
4	11
5	29
6	30
7	69
8	70
Carl London 2

Cell Formulas
Range	Formula
D1	D1	=A1>=70
E1:E3	E1	=FORMULATEXT(D1)
D2	D2	=AND(A1<70,A1>=30)
D3	D3	=AND(A2<30,A2>=10)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A8	Expression	=AND(A1<30,A1>=10)	text	NO
A1:A8	Expression	=AND(A1<70,A1>=30)	text	NO
A1:A8	Expression	=A1>=70	text	NO

 

[Carl_London]

Excellent, the people on here are so helpful.

Thank you,

Carl