what is .value = Mid(val,2,99)
supposed to do.
If the user enters a single digit, it makes the cell blank.
Not much for conditional formatting to operate on in that case.
Otherwise, turn off your error handler and see if your getting an error
which causes your code not to be executed.
--
Regards,
Tom Ogilvy
"Simon Lloyd " wrote in message
...
Hi all!,
I have some worksheet code on each sheet to perform a formatting
function in a certain range, i also have conditional formating set up
for that range to colour text, the range is formatted "wingdings" and
formatted custom I;I;I; so that any letter that is introduced is turned
in to a spot the conditional formatting colours the spot.....my problem
is this....when i run the macro it does not formatt the cell a colour
it does not enter the text input in to the input box ( the input box
requires number first and then a letter, the code works fine on a test
book but not when introduced to the sheet that has conditional
formatting) in the cell so leaving the cell blank can yo help?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim val
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("I3:AG641")) Is Nothing Then
val = InputBox("Enter Skill Level" & Chr(13) & "1= In Training" &
Chr(13) & "2= Trained" & Chr(13) & "3= Can Train Others" & Chr(13) &
"4= Delete Colour and Entry" & Chr(13) & "After number entry enter any
letter, For option 4 do not enter a letter!", "Skills Breakdown and
Competencies Entry", "")
'Range("A" & ActiveCell.Row).Select
With Target
Select Case Left(val, 1)
Case 1:
.Interior.ColorIndex = 48
.Value = Mid(val, 2, 99)
Case 2:
.Interior.ColorIndex = 41
.Value = Mid(val, 2, 99)
Case 3:
.Interior.ColorIndex = 43
.Value = Mid(val, 2, 99)
Case 4:
.Interior.ColorIndex = xlNone
.Value = Mid(val, 2, 99)
Case Else: MsgBox "Invalid Entry Try Again!"
'Range("A" & ActiveCell.Row).Select
End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
---
Message posted from http://www.ExcelForum.com/