I moved a file from work to home and now my color macro does not w

N

nick s

I moved a file from work to home and now my color macro does not work.
I have a macro for color changing cells that resides in the worksheet Tab
"View Code".
I didn't think there would be a problem, but I am wrong.
Is there a switch to turn on or something else I may need to do.


Here is the code, thanks to David McRitchie.....


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim vLetter As String
Dim vColor As Long
Dim yColor As Long
Dim cRange As Range
Dim cell As Range
'***************** check range ****
Set cRange = Intersect(Range("H2:H99"), Range(Target(1).Address))
If cRange Is Nothing Then Exit Sub
'**********************************

For Each cell In Target
vLetter = UCase(Left(cell.Value & " ", 3))
vColor = 0 'default is no color
yColor = xlColorIndexAutomatic
Select Case vLetter
Case "GF7"
vColor = 51
yColor = 2 ' white
Case "GY9"
vColor = 52
yColor = 2 ' white
Case "EV2"
vColor = 46
yColor = xlColorIndexAutomatic
Case "EL5"
vColor = 45
Case "FJ6"
vColor = 4
Case "GY8"
vColor = 12
yColor = 2 ' white
Case "FY1"
vColor = 6
Case "FY3"
vColor = 43
Case "GA4"
vColor = 47
yColor = 2 ' white
Case "FE5"
vColor = 3
Case "GB5"
vColor = 5
yColor = 2 ' white
Case "GK6"
vColor = 9
Case "GB7"
vColor = 11
yColor = 2 ' white
Case "GY4"
vColor = 12
Case "GE7"
vColor = 9
yColor = 2 ' white
Case "GF3"
vColor = 10
yColor = 2 ' white
Case "GT2"
vColor = 12
Case "GT8"
vColor = 52
yColor = 2 ' white
Case "EW1"
vColor = 2
Case "TX9"
vColor = 1
yColor = 2 ' white
Case "FC7"
vColor = 54
yColor = 2 ' white
End Select
Application.EnableEvents = False 'should be part of Change macro
cell.Interior.ColorIndex = vColor
cell.Font.ColorIndex = yColor
Application.EnableEvents = True 'should be part of Change macro
Next cell
'Target.Offset(0, 1).Interior.colorindex = vColor
' use Text instead of Interior if you prefer
End Sub
 
D

Dave Peterson

If you didn't play with the macro--move it to a different module, then I'd bet
it's your macro security that's changed.

Tools|macro|security|security level tab
Change it to something besides High.

Then close and reopen your workbook.

If it's not the macro security, maybe you had another macro that turned off
events and didn't turn them back on.

Close excel and reopen your workbook to test.

Or hit alt-f11 to get to the VBE
hit ctrl-g to see the immediate window
type this and hit enter:
application.enableevents = true

Then back to excel to test it out.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top