Conditional formatting not working!

[Simon Lloyd]

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

 

[Tom Ogilvy]

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.

 

[Simon Lloyd]

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!

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

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]

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

 

[Simon Lloyd]

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

 

[Bob Phillips]

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]

Kept the custom number anyway Bob but i now need the calculation of th
date that each condition of the CF was satisfying...think im gettin
myself in deep water with all this cos im losing sight of what iv
done, in CF the formula's were =AS3="" then text orange
=AS3+(I$2*365)>=TODA() then text black, =AS3+(I$2*365)<TODAY() the
text red, i still need this to happen, i tried your code seemed to wor
fine just need the above criteria now.

Meant what i said in the e-mail

Thanks
Simon

 

[Simon Lloyd]

Sorry Bob i mean to say that the CF formula i gave u was from cell I
and then format painted over the required range for each sheet

 

[Simon Lloyd]

Hi,

Ie been trying to get this worksheet macro to do the same job as th
conditional formattin above did, i have written some code (probably al
wrong!) that i would think would do the job (it doesnt work!) but if i
did it would only work for the absolute cell I$2 but the previous CF i
the active cell was say g54 it would look at the condition in G$2
other macros that i have working would put a date in an offset from th
activecell when run and the conditional formatting then looks at thes
cells and applies itself to the active cell.

Can anyone help!

Bob Philips has been kind enought to help me this far!

Simon

Heres the code!

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 an
letter, " & vbCrLf & "(For option 4 do not enter a letter!)", "Skill
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

If Intersect(Target, Me.Range("I3:AG641"), ActiveCell.Offset(0, 36)
"") Then
ActiveCell = Selection.ColorIndex = 3
ElseIf Intersect(Target, Me.Range("I3:AG641"), ActiveCell.Offset(0, 36
= ("I$2" * 365) >= Today()) Then
ActiveCell = Selection.ColorIndex = 4
ElseIf Intersect(Target, Me.Range("I:AG641"), ActiveCell.Offset(0, 36
= ("I$2" * 365) < Today()) Then
ActiveCell = Selection.ColorIndex = 5
End If

ws_exit:
Application.EnableEvents = True
End Sub

The if statements at the bottom here are what ive been trying to write
as i said if it did work it would only look at cell I$

 

[Simon Lloyd]

Can this macro be told to run after Conditional Formatting has take
place on a cell...maybe that way there would be no conflict and th
best of both worlds?

Simon

 

[Simon Lloyd]

can anyone help with the above Conditional formatting i need to do in
VBA?, im rally stuck and dont know enough about visual basic to sort
it!

Simon.