VBA/Highlight Active Row not Working in 2010 but does in 2016

[cmkarnes]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
    For Each aCell In ActiveSheet.UsedRange
        If aCell.Interior.ColorIndex = 8 Then aCell.Interior.ColorIndex = xlNone
    Next
    On Error Resume Next
    For Each aCell In Application.Intersect(ActiveCell.EntireRow.Cells, ActiveSheet.UsedRange)
        If aCell.Interior.ColorIndex = xlNone Then aCell.Interior.ColorIndex = 8
    Next
End Sub

Hello, I have an extremely large Excel 2016 Table on my home computer where I have vba that automatically highlights the active row when I click anywhere from within the row. It also does not disturb any other cell colors that already exist. I then took the same coding and copied it into a work Excel 2010 Table, and it will not fire. It is not clear to me what the issue would be since it works in 2016 perfectly but not in 2010. In the 2016 Table, Conditional Formatting was not used at all - just the vba alone made it work.

Any pointers would be greatly appreciated. Thank you.

 

[NARAYANK991]

Hi ,

I have tried out your code in Excel 2010 , and it works.

Upload your file if possible.

Narayan

 

[cmkarnes]

Hi Narayan, I'm having to put together a "mock" version of the Table which is what I'm doing now. Will forward in a bit. Thanks!

 

[p45cal]

You haven't got some (default) colouring in the Table have you?

edit:
I've just tried this and table colouring doesn't matter, however, if you've manually put a white background in the table or conditional formatting it won't show.

ps. …and you say it doesn't fire (so you've put a break point at the beginning of the code and it doesn't break there); have you checked that
Application.enableevents=true
?

 

[cmkarnes]

Hi, I've attached a very, very stripped down version of the Excel 2010 Table. I am not able to send the full workbook. Regardless, I threw in some conditional formatting on the attached as well. On my Excel 2016 Table on my personal computer, I've got conditional formatting in the Table, and this code works just fine with it. I did try the "Application Enable.Events = True and it made no difference (unless I'm just doing something wrong and don't realize it). Appreciate any assistance - thanks!

 

[p45cal]

You've managed to introduce a few typos in red (it can't have been a plain copy/paste (even the code you included in this thread would have worked):

If aCell.Interior.ColorIndex = 8 Then aCell.Interior.ColorIndex = x1None
Next
On Error Resume Next
For Each aCell In Application.Intersect(ActiveCell.EntireRow.Cells, ActiveSheet.UsedRange)
If aCell.Interior.ColorIndex = x1None Then aCell.InteriorColorIndex = 8

those 1s (ones) should be ls (letters L) and InteriorColorIndex needs to be Interior.ColorIndex

 

[cmkarnes]

Oh my gosh, I feel embarrassed - you are absolutely correct p45cal - the lower case "l" and the number "1" just looked the same to me, and I've learned another important aspect of coding which will aid me in going forward. I just went in and added the code to my 2010 excel project, and it seems to be working perfectly now with the corrections. Just in case I decide to do the row & column highlight, am playing around with it to see if I can make it do that as well. Many thanks!!!!