You could use worksheet change code.

Right click on the sheet tab, select View Code, and then past this code in

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
With Target
Select Case Left(.Value, 1)
Case 1:
.Interior.ColorIndex = 3
.Value = Mid(.Value, 2, 99)
Case 2:
.Interior.ColorIndex = 5
.Value = Mid(.Value, 2, 99)
Case 3:
.Interior.ColorIndex = 10
.Value = Mid(.Value, 2, 99)
Case 14:
.Interior.ColorIndex = 35
.Value = Mid(.Value, 2, 99)
Case Else: MsgBox "Invalid"
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Simon Lloyd " wrote in message
...
I want to create an input box for a range on a sheet (i understand i
have to do this on sheet code worksheet selection change) that will do
two things, firstly from inputing a number 1-4 format the cell a
different colour for each number then using the same input box allow
the user to enter any letter which will be put in the selected cell. So
i would like the input section of the box to be able to read something
like:- 1,h where 1 is the selection they made from 1-4 and the h is any
letter they wish!

Am i clear? im not sure...i know in my head what i want to do but
putting it into practice is difficult as i have only been using excell
for about 2 months and programming is relatively new!

All help no matter what appreciated.

Simon


---
Message posted from http://www.ExcelForum.com/