Its for filling in project status on a gantt chart style table. It is purely to make it simpler for the user and reduce human error by being able to select it in the cell. Is there any way of doing this. Conditional formatting would enable a choice of three colours, but i need 5.
Select the cells you want to colour and choose Data|Validation from the menu. In the Allow box choose List. In the Source box type:
and click OK.
Right click the sheet tab and choose View Code. Paste this code into the window on the right:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Temp As String
On Error Resume Next
Temp = Target.Validation.Formula1
If Err <> 0 Then Exit Sub
Select Case Target.Value
Target.Interior.ColorIndex = 3
Target.Font.ColorIndex = 3
Target.Interior.ColorIndex = 10
Target.Font.ColorIndex = 10
Target.Interior.ColorIndex = 5
Target.Font.ColorIndex = 5
Target.Interior.ColorIndex = 6
Target.Font.ColorIndex = 6
Target.Interior.ColorIndex = 53
Target.Font.ColorIndex = 53
Press Alt+F11 to return to your worksheet and try it out.
This does not seem to work. When you select an option from the dropdown list nothing happens except it enters the word "red" etc in a black font. If you type the words "red" etc in rather than using the dropdown it brings up a nice box that says "hello", however the formatting is not changed.