Hi all!,

I have some worksheet code on each sheet to perform a formattin
function in a certain range, i also have conditional formating set u
for that range to colour text, the range is formatted "wingdings" an
formatted custom I;I;I; so that any letter that is introduced is turne
in to a spot the conditional formatting colours the spot.....my proble
is this....when i run the macro it does not formatt the cell a colou
it does not enter the text input in to the input box ( the input bo
requires number first and then a letter, the code works fine on a tes
book but not when introduced to the sheet that has conditiona
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 an
letter, For option 4 do not enter a letter!", "Skills Breakdown an
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 Su

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

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/

Thanks for replying Tom,

the code was supplied by someone else i have just added my own bits t
it as you know im new to this, in the input box that pops up i wante
the user to be able to enter first a number followed by a letter (didn
matter which letter as i have formatted the cells in thi
fashion:-Wingdings, Custom format I;I;I;) so that when the cas
scenario picked up the number it would format the cell the appropriat
colour and the letter would then be placed in the cell....i did the
think that the conditional formatting built in to excell would do th
rest but neither of the formatting works in this work book however th
code i have supplied works fine in a test work book with ou
conditional formatting.

Simon

p.s dont know how to turn error handling on or off!

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

Simon,

Your original question was to have the user input a value with a number,
which would drive the cell colour, and a letter, which would be placed in
the cell (I just allowed for more than 1 character). As it caters for more
than 3 colours, conditional formatting cannot handle it, and as the code
colours the cell, you don't need CF.

You also said that it worked as required, so what has changed?

--

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
...
Thanks for replying Tom,

the code was supplied by someone else i have just added my own bits to
it as you know im new to this, in the input box that pops up i wanted
the user to be able to enter first a number followed by a letter (didnt
matter which letter as i have formatted the cells in this
fashion:-Wingdings, Custom format I;I;I;) so that when the case
scenario picked up the number it would format the cell the appropriate
colour and the letter would then be placed in the cell....i did then
think that the conditional formatting built in to excell would do the
rest but neither of the formatting works in this work book however the
code i have supplied works fine in a test work book with out
conditional formatting.

Simon

p.s dont know how to turn error handling on or off!!


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

Hi Bob,

It worked in the test book where conditional formatting wasnt in place
if you read my last post i explain how i have formatted the range
firstly i wanted the workbook idiot proof (i place myself in tha
catagory!) so i used conditional formating so people could tell
variety of things by the three colours, but these were to be th
colours of the text (spots as they will be because wingdings capital
is a spot) but it seeems that you cannot have a case arguement workin
for colours if you have conditional formatting running as they seem t
cancel each other out, i need the number in put to colour the whol
cell and the letter to input into the cell so that conditiona
formatting would take over and colour the text....however they don
work together, i have tried using a statement on the worksheet chang
module like Selection.FormatConditions = False and then true after bu
it doeant support the method or property.

I'm at work and sinkin fast!......the above line i tried the macro run
til it has runtime error if i end debug the built in CF takes over an
i can now get spots but the code u gave is disabled!

Looking forward to your reply!

Simon

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

Sim on ,

Here is anoither shot before you drown. Things to note

- you can only enter 1,2,3, or 4 as fist character
- if you enter 4, noithing else
- colour dependent on 1 2 3 and 4 turns off
- not done anything with 2nd char except load the cell with it and changed
the font to Wingdings (shouyld there be special action depending upoin what
is entered)

SO get rid of CF and add this.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim val
Dim fValid As Boolean
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("I3:AG641")) Is Nothing Then
Do
fValid = False
val = InputBox("Enter Skill Level" & vbCrLf & _
" 1= In Training" & vbCrLf & _
" 2= Trained" & vbCrLf & _
" 3= Can Train Others" & vbCrLf & _
" 4= Delete Colour and Entry" & vbCrLf & _
"After number entry enter any letter, " & vbCrLf &
_
"(For option 4 do not enter a letter!)", _
"Skills Breakdown and Competencies Entry", "")
If val = "" Then fValid = True
If Len(val) = 2 Then
If Left(val, 1) = 1 Or Left(val, 1) = 2 Or Left(val, 1) = 3
Then
fValid = True
End If
ElseIf Len(val) = 1 Then
If Left(val, 1) = 4 Then fValid = True
End If
If Not fValid Then _
MsgBox "Invalid Entry Try Again!"
Loop Until fValid
With Target
Select Case Left(val, 1)
Case 1:
.Interior.ColorIndex = 48
Case 2:
.Interior.ColorIndex = 41
Case 3:
.Interior.ColorIndex = 43
Case 4:
.Interior.ColorIndex = xlNone
.Value = ""
.Font.Name = "Times New Roman"
Exit Sub
End Select
Select Case Mid(val, 2, 1)
Case Else:
.Value = Mid(val, 2, 1)
.Font.Name = "Wingdings"
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
...
Thanks for replying Tom,

the code was supplied by someone else i have just added my own bits to
it as you know im new to this, in the input box that pops up i wanted
the user to be able to enter first a number followed by a letter (didnt
matter which letter as i have formatted the cells in this
fashion:-Wingdings, Custom format I;I;I;) so that when the case
scenario picked up the number it would format the cell the appropriate
colour and the letter would then be placed in the cell....i did then
think that the conditional formatting built in to excell would do the
rest but neither of the formatting works in this work book however the
code i have supplied works fine in a test work book with out
conditional formatting.

Simon

p.s dont know how to turn error handling on or off!!


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

I haven't used the code yet Bob, i already have the range on each pag
formatted as windings and when you right click cell and choose format
numbers, custom i have entered the custom I;I;I; this hides the I bu
ensures that any charachter entered becomes the Capital letter I (i
wingdings its a spot) and i dont want to change them back to any othe
font format....if you look at the workbook i sent you under condition
formatting you will see the thre arguments something like:- =AS"" the
format text orange and the next is =AS*I$2(365) then text black and s
on for the third where the text is changed red (these arent exac
formula quotes!, but you hae them in the workbook there) try it
disable the worksheet code on 1 worksheet and enter any character
like in the cells to the right of skills under the muddy orange titles
when this sheet is then updated it will turn the orange dot black an
if the date in date view is older than 18 months then it turns red
thats what i need to still happen and with the added advent of thi
input box i want the cell colour to change as per the selected numbe
in the in put box....so after all that waffle if i get rid of CF an
get rid of custom number the special character would be capita
I,.....didnt i waffle some to get there?

Ive been dodging the training department today cos its not ready <g

Simo

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

The spot when entered is supposed to stay the colours mentioned unti
updated by clicking th update button in the workbook or shutting do
the work book so the user can see which is a new entry (orange) whic
is an updated entry (black) and which entry has lapsed its time limi
which is set by the value in row 2 i.e 1.5 or 2 etc

Simo

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

I didn't say get rid of custom number if that works for you keep it, but the
CF is getting in the way.

--

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 haven't used the code yet Bob, i already have the range on each page
formatted as windings and when you right click cell and choose format,
numbers, custom i have entered the custom I;I;I; this hides the I but
ensures that any charachter entered becomes the Capital letter I (in
wingdings its a spot) and i dont want to change them back to any other
font format....if you look at the workbook i sent you under conditiona
formatting you will see the thre arguments something like:- =AS"" then
format text orange and the next is =AS*I$2(365) then text black and so
on for the third where the text is changed red (these arent exact
formula quotes!, but you hae them in the workbook there) try it,
disable the worksheet code on 1 worksheet and enter any character u
like in the cells to the right of skills under the muddy orange titles,
when this sheet is then updated it will turn the orange dot black and
if the date in date view is older than 18 months then it turns red,
thats what i need to still happen and with the added advent of this
input box i want the cell colour to change as per the selected number
in the in put box....so after all that waffle if i get rid of CF and
get rid of custom number the special character would be capital
I,.....didnt i waffle some to get there?

Ive been dodging the training department today cos its not ready <g

Simon


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