Drop Down Menu (Just Back ground colours)

[Gjdr]

I am wanting to create a drop down list so i can change the colours of the background of the cells only, not change the information that is in the cell. The reason for this is, i have a column with $$ values in them and we change the background colour of the cells( up to 6 colours) so we can determine what stage a project is at by the colour background. I understand how to make a drop down list using Data etc but this has beaten me. Any help would be appreciated.

 

[Yongle]

welcome to MrExcel

Not quite sure what you want
- but it can be achieved using conditional formatting

Does the dropdown cell contain a list of the 6 stages ?
Does the user select one of the 6 stages ?
Do you want to change the background colour of dropdown cell or a different cell in the same row ?

 

[Gjdr]

Hi Yongle

the drop down list could contain a list stages, Stage 1, Stage 2, Stage 3 etc. As an example if a user on the sheet clicked on a cell that had a $ figure in it, he could then choose the drop down list and click on any of the stages listed. Let say he picked stage 1, and the the back ground then change colour to the one we require it to, but not wipe out the $ figure in the same cell.

 

[Yongle]

... which column contains dropdown cell?
... which column contains cell where background colour must change based on value selected from dropdown?

 

[Gjdr]

hyper thetically say the column i am using is M3 down to M100, this column has all the $ values in it. It is the same column that i want the back ground to change but keep the $ value

 

[Yongle]

You answered only one question.

 

[Yongle]

Select cells M3:M100 and set up 6 conditional formatting rules - one for each stage

Book1
	L	M
2	Dropdown	$Values
3	A	39.39
4	B	52.52
5	C	65.65
6	D	78.78
7	E	91.91
8	F	105.04
Sheet15

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M3:M100	Expression	=L3="F"	text	NO
M3:M100	Expression	=L3="E"	text	NO
M3:M100	Expression	=L3="D"	text	NO
M3:M100	Expression	=L3="C"	text	NO
M3:M100	Expression	=L3="B"	text	NO
M3:M100	Expression	=L3="A"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
L3:L8	List	A,B,C,D,E,F

 

[Gjdr]

thanks Yongle

i will try and have a crack at this on the weekend