Macro works in 2000 but not in 2016

[tthdoc]

I have a script that I have used for years with Excel 2000 for conditional formatting since in that version you were limited to 3 choices. On one of the computers we updated to Excel 2016 and the script no longer works. If you type a letter in a cell, for instance F or f, the script will change the color of the cell and the font color. It will also capitalize the letter, so f will be F. If I type the letter F or f in a cell now, I get the following error:

Method 'ColorIndex' of object 'Interior' failed

I know I can set up conditional formatting for many types with 2016, but then it will not work with 2000. Is there a way to fix this so it will work with both versions, or can you have it check the version you use before executing the script?

Here is the script:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Column > 50 Or Target.Cells.Count > 1 Then Exit Sub
With Target.Interior
.ColorIndex = 10
Select Case UCase(Target.Value)
Case "L"
Target.Value = UCase(Target.Value)
.ColorIndex = 24
Target.Font.Color = vbBlack
Case "F"
Target.Value = UCase(Target.Value)
.ColorIndex = 41
Target.Font.Color = vbWhite
Case "H"
Target.Value = UCase(Target.Value)
.ColorIndex = 10
Target.Font.Color = vbWhite
Case "V"
Target.Value = UCase(Target.Value)
.ColorIndex = 6
Target.Font.Color = vbBlack
Case "A"
Target.Value = UCase(Target.Value)
.ColorIndex = 46
Target.Font.Color = vbWhite
Case "D"
Target.Value = UCase(Target.Value)
.ColorIndex = 17
Target.Font.Color = vbWhite

Case "X"
Target.Value = UCase(Target.Value)
.ColorIndex = 0
Target.Font.Color = vbBlack
Case Else
.ColorIndex = 0
End Select
End With
End Sub

Thanks,
Doc

 

[Fluff]

Hi & welcome to MrExcel.
Your code works for me in 2013 & I can see no reason why it wouldn't work in 2016
What line does it fail on?

 

[tthdoc]

.ColorIndex = 10 (right in the beginning, right after With Target.Interior.)

 

[Fluff]

As that line doesn't actually do anything, you might as well delete it, but I suspect that it may then fail on another line.

Try this for the "F" & "L"

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   If Target.Column > 50 Or Target.Cells.Count > 1 Then Exit Sub
   With Target.Interior
      Select Case UCase(Target.Value)
         Case "L"
            Target.Value = UCase(Target.Value)
            .Color = RGB(204, 204, 255)
            Target.Font.Color = vbBlack
         Case "F"
            Target.Value = UCase(Target.Value)
            .Color = RGB(51, 102, 255)
            Target.Font.Color = vbWhite
         Case "H"
            Target.Value = UCase(Target.Value)
            .ColorIndex = 10
            Target.Font.Color = vbWhite
         Case "V"
            Target.Value = UCase(Target.Value)
            .ColorIndex = 6
            Target.Font.Color = vbBlack
         Case "A"
            Target.Value = UCase(Target.Value)
            .ColorIndex = 46
            Target.Font.Color = vbWhite
         Case "D"
            Target.Value = UCase(Target.Value)
            .ColorIndex = 17
            Target.Font.Color = vbWhite
         Case "X"
            Target.Value = UCase(Target.Value)
            .ColorIndex = 0
            Target.Font.Color = vbBlack
         Case Else
            .ColorIndex = 0
      End Select
   End With
End Sub

 

[tthdoc]

I changed the script and still no go. If I remove the script and use conditional formatting built it with 2016 it allows me to set all the colors and works fine If I leave the script in, it does not work. If I delete all the conditional formatting items, and just have the script, I get the same error:

Method 'Color' of object 'Interior' failed

I can make it work with 2016, but I have other computers with 2000 on them and need it to work for them.

Here is the updated script (which works fine in 2000, but not 2016)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Column > 50 Or Target.Cells.Count > 1 Then Exit Sub
With Target.Interior
Select Case UCase(Target.Value)
Case "L"
Target.Value = UCase(Target.Value)
.Color = RGB(204, 204, 225)
Target.Font.Color = vbBlack
Case "F"
Target.Value = UCase(Target.Value)
.Color = RGB(51, 102, 255)
Target.Font.Color = vbWhite
Case "H"
Target.Value = UCase(Target.Value)
.Color = RGB(0, 128, 0)
Target.Font.Color = vbWhite
Case "V"
Target.Value = UCase(Target.Value)
.Color = RGB(255, 255, 0)
Target.Font.Color = vbBlack
Case "A"
Target.Value = UCase(Target.Value)
.Color = RGB(255, 102, 0)
Target.Font.Color = vbWhite
Case "D"
Target.Value = UCase(Target.Value)
.Color = RGB(153, 153, 255)
Target.Font.Color = vbWhite
Case "X"
Target.Value = UCase(Target.Value)
.Color = RGB(255, 255, 255)
Target.Font.Color = vbBlack
Case Else
.Color = RGB(255, 255, 255)
End Select
End With
End Sub

I know you said it works in 2013 and should work in 2016, but obviously it does not work in 2016. Any other suggestions>

 

[tthdoc]

Ok, I solved the issue.

I added as the first line

If Val(Application.Version) = 16 Then Exit Sub

Now the script will run for my 2000 version, but will not run for the 2016 and the built in conditional formatting for 16 will work instead.

 

[ZVI]

Hi,
It is required to switch events triggering off/on because the code changes values of the cells which triggers that code again.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  If Target.Column > 50 Or Target.Cells.Count > 1 Then Exit Sub
  Application.EnableEvents = False  ' Disable events triggering
  With Target.Interior
    ' ... your code ...
  End With
  Application.EnableEvents = True   ' Enable events triggering
End Sub

Regards

 

[tthdoc]

Not sure what you are saying. I have modified the code and all is working fine. The only problem I ran into is that the original code also capitalized everything and with conditional formatting in 2016 you can't do that. So I further modified the code to see if it was 2016 and if so, capitalize, if not, just end the routine. In any event, the code is working fine with both 2000 and 2016, so can you explain why I would need to add the EnableEvents code? Here is the new code that seems to work fine:

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


If Target.Column > 50 Or Target.Cells.Count > 1 Then Exit Sub


If Val(Application.Version) = 16 Then
With Target.Interior
Select Case UCase(Target.Value)
Case "L"
Target.Value = UCase(Target.Value)
Case "F"
Target.Value = UCase(Target.Value)
Case "H"
Target.Value = UCase(Target.Value)
Case "V"
Target.Value = UCase(Target.Value)
Case "A"
Target.Value = UCase(Target.Value)
Case "D"
Target.Value = UCase(Target.Value)
Case "X"
Target.Value = UCase(Target.Value)
End Select
End With
End If


If Val(Application.Version) = 16 Then Exit Sub


If Target.Column > 50 Or Target.Cells.Count > 1 Then Exit Sub
With Target.Interior
Select Case UCase(Target.Value)
Case "L"
Target.Value = UCase(Target.Value)
.Color = RGB(204, 204, 225)
Target.Font.Color = vbBlack
Case "F"
Target.Value = UCase(Target.Value)
.Color = RGB(51, 102, 255)
Target.Font.Color = vbWhite
Case "H"
Target.Value = UCase(Target.Value)
.Color = RGB(0, 128, 0)
Target.Font.Color = vbWhite
Case "V"
Target.Value = UCase(Target.Value)
.Color = RGB(255, 255, 0)
Target.Font.Color = vbBlack
Case "A"
Target.Value = UCase(Target.Value)
.Color = RGB(255, 102, 0)
Target.Font.Color = vbWhite
Case "D"
Target.Value = UCase(Target.Value)
.Color = RGB(153, 153, 255)
Target.Font.Color = vbWhite
Case "X"
Target.Value = UCase(Target.Value)
.Color = RGB(255, 255, 255)
Target.Font.Color = vbBlack
Case Else
.Color = RGB(255, 255, 255)
End Select
End With
End Sub

 

[ZVI]

Not sure what you are saying. ... can you explain why I would need to add the EnableEvents code?

I meant that your code in the post 5 should be modified like this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  If Target.Column > 50 Or Target.Cells.Count > 1 Then Exit Sub
  Application.EnableEvents = False  ' Disable events triggering
  With Target.Interior
    Select Case UCase(Target.Value)
      Case "L"
      Target.Value = UCase(Target.Value)
      .Color = RGB(204, 204, 225)
      Target.Font.Color = vbBlack
      Case "F"
      Target.Value = UCase(Target.Value)
      .Color = RGB(51, 102, 255)
      Target.Font.Color = vbWhite
      Case "H"
      Target.Value = UCase(Target.Value)
      .Color = RGB(0, 128, 0)
      Target.Font.Color = vbWhite
      Case "V"
      Target.Value = UCase(Target.Value)
      .Color = RGB(255, 255, 0)
      Target.Font.Color = vbBlack
      Case "A"
      Target.Value = UCase(Target.Value)
      .Color = RGB(255, 102, 0)
      Target.Font.Color = vbWhite
      Case "D"
      Target.Value = UCase(Target.Value)
      .Color = RGB(153, 153, 255)
      Target.Font.Color = vbWhite
      Case "X"
      Target.Value = UCase(Target.Value)
      .Color = RGB(255, 255, 255)
      Target.Font.Color = vbBlack
      Case Else
      .Color = RGB(255, 255, 255)
    End Select
  End With
  Application.EnableEvents = True   ' Enable events triggering
End Sub

After that it will work in any version of Excel without conditional formatting.

 

[ZVI]

The explanation was here:

... because the code changes values of the cells which triggers that code again.

That is, here is the scenario:
1. Enter symbol "v" or "V" into the cell
2. Code of Workbook_SheetChange runs
3. The running code rewrites value in that cell
4. Rewriting is the changing event, therefore goto point 2, etc infinite times.

As the result the infinite loop can happen, but in Excel 2016 it ends with error after some recursions.
In Excel 2000 that recursion does not happen because of old engine of recalculations.

Application.EnableEvents = False disables any event triggering and prevents rerunning of the point 2 after point 4.
Application.EnableEvents = True at the end of code restores triggering after events.