My code isn't working now

[TrippyTom]

ACK! Help!

I have been working on my vba code while at work, and wanted to work on it at home, so I emailed it to myself, made some edits at home, and emailed it back.

However, I have a higher version of Excel at home and I'm wondering if that might have screwed up my code, because IT'S BEING IGNORED NOW!

Is there a way to fix it? Or do I have to wait until I get home, save as a lower version, then resend it?

 

[Oaktree]

Some VBA code from later versions of Excel doesn't work on earlier versions of Excel.

Care to post your code?

 

[TrippyTom]

I have Excel 2003 at home.
We use Excel 2000 at work.

It seems to be ignoring the worksheet_change event when I edit cell B5. It did yesterday, just fine. I'm not sure what could have changed since I saved it at home.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If ActiveCell.Address = Range("B5").Address Then
    Calculate
    For Each c In Union([E6:K11], [U6:AA11], _
                        [E15:K20], [M15:S20], [U15:AA20], _
                        [E24:K29], [M24:S29], [U24:AA29], _
                        [E33:K38], [M33:S38], [U33:AA38]).Cells
        
        c.Interior.ColorIndex = 2 'white
        c.Font.Bold = False
        c.NumberFormat = "General"
        c.Font.ColorIndex = cUnusedColorIdx
        
        Select Case c.Value
            Case Is = 15
                c.Interior.ColorIndex = 4 'green
                c.Font.Bold = True
                c.Font.ColorIndex = 2 'white
            Case Is = 30
                c.Interior.ColorIndex = 4
                c.Font.Bold = True
                c.Font.ColorIndex = 2 'white
            Case Else
        End Select
    Next
End If

'................................................. SPECIAL CASE FOR FEBRUARY
If ActiveCell.Address = Range("B5").Address Then
    
    For Each c In Range("M6:S11").Cells
        
        c.Interior.ColorIndex = 2 'white
        c.Font.Bold = False
        c.NumberFormat = "General"
        c.Font.ColorIndex = cUnusedColorIdx
        
        Select Case c.Value
            Case Is = 15
                c.Interior.ColorIndex = 4 'green
                c.Font.Bold = True
                c.Font.ColorIndex = 2 'white
            Case Is = Range("AN4") 'This uses the EOMONTH for Leap Year functionality
                c.Interior.ColorIndex = 4
                c.Font.Bold = True
                c.Font.ColorIndex = 2 'white
            Case Else
        End Select
    Next
End If

End Sub

 

[Norie]

What's not working? Is it causing errors or not doing what you expect?

BTW could you not use conditional formatting to do what you want.

 

[TrippyTom]

It's not updating at all. It seems to be ignoring the worksheet_change event.

I can't use conditional formatting because eventually I need to add code to move the payday if it falls on a weekday or holiday. I was just getting this to work for now.

 

[Norie]

Is the code actually being triggered?

Is calculation set to Manual?

 

[Zack Barresse]

You may have had your events turned off by some erroneous code. Try to reset these items ...

Sub Reset_App_Atrributes()
    With Application
        .DisplayAlerts = True
        .EnableEvents = True
        .ScreenUpdating = True
        .StatusBar = True
    End With
End Sub

Let us know if running this routine helps. Run prior to trying your change event. Ensure you put this routine into a Standard Module to run.

 

[TrippyTom]

The code isn't being triggered because the green (paydays) aren't refreshing at all. Calculation is forced to recalculate in my code before the payday routines.

I can tell it's ignoring the code though because my conditional formatting for the holidays refreshes fine, but the paydays don't move when I enter a new year in B5.

 

[TrippyTom]

Hmm.. I inserted a new module, copied that code into it and ran it.
Then I tried a new year and it's still not updating.

I don't know how I always find a way to royally mess things up... I seem to have a knack for it!

 

[Zack Barresse]

And the code is in the correct worksheet module? And you are trying this on cell B5?