Changing case in code

[Terry]

I've added a line of code to the event below that will uppercase the data in
the last-edited cell but the update takes a few seconds on my PC (probably
longer on the production pc it will eventually run on). Is there a way i can
better place the Ucase code? I've tried, unsuccessfully, to move it outside
the For/Next loop. I won't pretend to understand the Set statement.

Any help would be gratefully appreciated
Terry

Private Sub Worksheet_Change(ByVal Target As Range)
'David McRitchie, 2000-08-08 rev. 2000-08-14
'http://www.mvps.org/dmcritchie/excel/event.htm
'with modifications (TwH)

Dim vLetter As String
Dim vColour As Integer
Dim fColour As Integer
Dim cRange As Range
Dim cell As Range
'***************** check range ****
Set cRange = Intersect(Range("All_Shifts_1"), Range(Target(1).Address))
If cRange Is Nothing Then Exit Sub
'**********************************
For Each cell In Target
vLetter = Len(ActiveCell.Value)
vColour = 3
fColour = 1
Select Case vLetter
Case Is = 0
vColour = 3
Case Is < 5
vColour = 4
Case Is > 5
vColour = 5
fColour = 2
End Select
cell.Interior.ColorIndex = vColour
cell.Font.ColorIndex = fColour
cell.Value = UCase(cell.Value) '********U C A S E
Next cell
'Target.Offset(0, 1).Interior.colorindex = vColour
'use Text instead of Interior if you prefer
End Sub

 

[Dave Peterson]

This worksheet_change fires when a cell's value changes. It doesn't fire when
you change formats.

But since you're now changing values, when you change a value, it fires the
macro again. Which changes the value and causes another firing .... until excel
gets tired and gives up.

You can add a couple of lines to stop the firing:

Application.enableevents = false
cell.Value = UCase(cell.Value)
application.enableevents = true

This causes xl to stop looking for changes for that one line.

===
And I think that there's a small bug in David's code.

I think that this line:
For Each cell In Target

should be
For Each cell In cRange

You only want to change the cells that are in that "all_shifts_1" range???

 

[Terry]

I tried wrapping the whole macro in Application.enableevents false|true last
night but the results i got were spurious, maybe it was too late to be
working on code i didn't fully understand. It works ok today though, thanks
for that.

Yes, i only want to effect changes to the named range so i made the For Each
change you suggested. The line below this should have read vLetter =
Len(cell.Value) not vLetter = Len(ActiveCell.Value) - whiich i changed early
on while trying to 'fix' the original problem.

Thanks again for your help.
Terry