| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I moved a file from work to home and now my color macro does not w
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I moved a file from work to home and now my color macro does not w
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. nick s wrote: 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 -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
I moved a file from work to home and now my color macro does n
thank you, that was the problem, security was set on high.....
"Dave Peterson" wrote: 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. nick s wrote: 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 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
