Workbook_SheetChange(ByVal Target As Range) event suddenly not working?

[Sniperbooya]

Hi guys,

Last week a friend of mine asked me to write a little code to change the font color of a certain range when the conditions of that certain cell are met.

So i wrote him the following code and everything was working fine:

Private Sub Workbook_SheetChange(ByVal Target As Range)
Dim c As Range
For Each c In Range("I1:I485").Cells
  c.Select
  If c.Value = "Binnendijks" Then
  ActiveSheet.Unprotect Password:="01FGSlab1962"
    With Selection.Font
      c.Font.Color = RGB(76, 153, 0)
    End With
    ActiveSheet.Protect Password:="01FGSlab1962"
  End If
Next
Dim c2 As Range
For Each c2 In Range("I1:I485").Cells
  c2.Select
  If c2.Value = "Buitendijks" Then
  ActiveSheet.Unprotect Password:="01FGSlab1962"
    With Selection.Font
      c2.Font.Color = RGB(255, 128, 0)
    End With
    ActiveSheet.Protect Password:="01FGSlab1962"
  End If
Next
Range("A13").Select
End Sub

But as of today the code is not working anymore. Well at least the

Workbook_SheetChange(ByVal Target As Range)

bit is not working anymore.

When I put the same code 'under' a button it works perfectly.

I thought maybe i disabled the events but even after manually enabling the events it doesnt work.

Any ideas?

Thanks !

 

[Brianwarnock]

I could not get it to fail.
I did alter the code for testing but that should not have mattered, I then took the liberty of rewriting the code to make it more efficient and to only run the checks if the change was in the range.

Private Sub Workbook_SheetChange(ByVal Target As Range)
'MsgBox "hello"
If Application.Intersect(Target, Range("i1:i14")) Is Nothing Then
    Exit Sub
End If
'ActiveSheet.Unprotect Password:="01FGSlab1962"
Dim c As Range
For Each c In Range("I1:I14").Cells
   If c.Value = "abc" Then
      c.Font.Color = RGB(76, 153, 0)
   ElseIf c.Value = "qwe" Then
      c.Font.Color = RGB(255, 128, 0)
  End If
Next c
'ActiveSheet.Protect Password:="01FGSlab1962"
Range("A13").Select
End Sub

I also tried it in the Worksheet_Change event of the sheet I was using.

Brian

 

[Brianwarnock]

The most efficient code would be to only check the cells that are changed in the range, but none of this helps solve your problem, I Googled it but none of the answers seemed to fit your simple Workbook. I have assumed that you have no other code .

Just for info here is my latest code, this allows you to drag down or do multiple paste following a copy.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Target, Range("i1:i14")) Is Nothing Then
 For Each c In Target
   If c.Value = "abc" Then
      c.Font.Color = RGB(76, 153, 0)
    ElseIf c.Value = "qwe" Then
       c.Font.Color = RGB(255, 128, 0)
   End If
  Next c
Application.CutCopyMode = False
Else
    Exit Sub
End If
End Sub

 

[RayH]

The Workbook_SheetChange sub takes two parameters:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

The 'Sh' variable is used like this:
For Each c In Sh.Range("I1:I485").Cells

With this change the original code works fine.

But Brian's updates are more efficient.

 

[Brianwarnock]

I agree with Ray and was surprised when I copied and pasted the original code into my spreadsheet , did the mods for my test and it worked, but guess what the workbook version fails today with the compile error message
procedure declaration does not match description of event or procedure having the same name

which is what I would have expected originally.

adding in the extra parameter even if you don't use it and it works, but Ry is correct to use it.

Personally I would use the Worksheet_Change event for the sheet unless I wanted to cause a change to one sheet from another.

Brian

 

[Sniperbooya]

Hi guys,

Thanks for all the replies.

I just tried several things (including the codes above) and it still does not fire the code when the value changes.

However, when i create a new Excelworksheet the code works perfectly.
I am started to think the Excel file i originally used got corrupted somehow..

I will just start from scratch since i simply do not have the time to keep busting my head on my 'corrupted' worksheet.

I originally thought it had something to do with the Microsoft Update December 2014 issue, but after doing the fixes posted on the Microsoft website it still wouldn't work.

Anyhow, thanks to all of you for taking the time and effort replying to my question. You guys are the best.