VBA code fail Auto highlight

%3CLINGO-SUB%20id%3D%22lingo-sub-1222713%22%20slang%3D%22en-US%22%3EVBA%20code%20fail%20Auto%20highlight%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1222713%22%20slang%3D%22en-US%22%3EI%20entered%20code%20for%20auto%20fill%20which%20worked%20(copied%20and%20pasted%20from%20google)%3CBR%20%2F%3E%3CBR%20%2F%3EI%20saved%20and%20closed%20but%20when%20I%20went%20to%20open%20it%20stopped%20working.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20now%20can%E2%80%99t%20apply%20the%20code%20to%20any%20spreadsheet.%3CBR%20%2F%3E%3CBR%20%2F%3EHow%20can%20I%20fix%20that%3F%20Code%20below%3A%3CBR%20%2F%3E%3CBR%20%2F%3EPrivate%20Sub%20Worksheet_SelectionChange(ByVal%20Target%20As%20Range)%3CBR%20%2F%3EOn%20Error%20Resume%20Next%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20False%3CBR%20%2F%3E'%20Clear%20the%20color%20of%20all%20the%20cells%3CBR%20%2F%3ECells.Interior.ColorIndex%20%3D%200%3CBR%20%2F%3EIf%20Target.Cells.Count%20%26gt%3B%201%20Then%20Exit%20Sub%3CBR%20%2F%3EWith%20Target%3CBR%20%2F%3E'%20Highlight%20the%20entire%20row%20and%20column%20that%20contain%20the%20active%20cell%3CBR%20%2F%3E.EntireRow.Interior.ColorIndex%20%3D%206%3CBR%20%2F%3E.EntireColumn.Interior.ColorIndex%20%3D%206%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3EApplication.ScreenUpdating%20%3D%20True%3CBR%20%2F%3EEnd%20Sub%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1222713%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1222828%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20code%20fail%20Auto%20highlight%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1222828%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F48368%22%20target%3D%22_blank%22%3E%40Laura%20Backshell%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDid%20you%20save%20the%20file%20as%20Macro-Enabled%20Workbook%3F%3C%2FP%3E%3CP%3EPoint%20to%20remember%20is%2C%20only%20.xls%20or%20.xlsb%20or%20.xlsm%20file%20can%20hold%20the%20macro%20in%20it.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1222856%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20code%20fail%20Auto%20highlight%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1222856%22%20slang%3D%22en-US%22%3EHi%3CBR%20%2F%3E%3CBR%20%2F%3EYeah%2C%20even%20when%20I%20apply%20to%20a%20new%20file%20or%20save%20and%20open%20as%20xsl%20etc%20it%20still%20doesn%E2%80%99t%20apply.%20Saves%20the%20code%20fine%20but%20doesn%E2%80%99t%20make%20a%20difference.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1224006%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20code%20fail%20Auto%20highlight%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1224006%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F48368%22%20target%3D%22_blank%22%3E%40Laura%20Backshell%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheck%20the%20attached%20in%20which%20I%20have%20placed%20the%20code%20on%20Sheet1%20Module%2C%20let%20me%20know%20if%20that%20works%20for%20you.%3C%2FP%3E%3CP%3EMake%20sure%20to%20enable%20the%20macros%20in%20the%20file%20when%20prompted.%20If%20macros%20are%20disabled%2C%20they%20won't%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1227107%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20code%20fail%20Auto%20highlight%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1227107%22%20slang%3D%22en-US%22%3EHi%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20code%20worked%20fine%20when%20I%20used%20your%20spreadsheet%2C%20but%20when%20I%20opened%20a%20new%20excel%20file%20and%20added%20the%20module%20it%20didn%E2%80%99t%20work.%3CBR%20%2F%3E%3CBR%20%2F%3EHow%20do%20I%20make%20it%20active%20in%20the%20spreadsheet%3F%20It%E2%80%99s%20Saved%20as%20macro-enabled%20before%20I%20added%20the%20code%20but%20it%E2%80%99s%20not%20highlighting%20the%20rows.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1227449%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20code%20fail%20Auto%20highlight%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1227449%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F48368%22%20target%3D%22_blank%22%3E%40Laura%20Backshell%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20selection%20change%20event%20code%20which%20gets%20triggered%20automatically%20when%20you%20change%20the%20selection%20on%20the%20sheet%20and%20%3CU%3E%3CSTRONG%3Eit%20should%20be%20placed%20on%20the%20Sheet%20Module%3C%2FSTRONG%3E%3C%2FU%3E%20but%20not%20on%20a%20standard%20module%20like%20Module1.%3C%2FP%3E%3CP%3ETo%20place%20the%20code%20on%20to%20the%20Sheet%20Module%2C%20right%20click%20on%20the%20Sheet%20Tab%20Name%20and%20choose%20%3CSTRONG%3EView%20Code%3C%2FSTRONG%3E%20and%20then%20paste%20the%20code%20into%20the%20opened%20code%20window%2C%20save%20your%20file%20as%20Macro-Enabled%20Workbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20file%20I%20uploaded%20with%20the%20code%20would%20work%20each%20time%20you%20open%20it%20because%20the%20code%20was%20placed%20on%20the%20Sheet%20Module.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1227506%22%20slang%3D%22en-US%22%3ERE%3A%20VBA%20code%20fail%20Auto%20highlight%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1227506%22%20slang%3D%22en-US%22%3EI%20want%20my%20headers%20to%20stay%20at%20top%20of%20page%20as%20scrolling%20down.%20How%20do%20I%20do%20that%3F%3C%2FLINGO-BODY%3E
Contributor
I entered code for auto fill which worked (copied and pasted from google)

I saved and closed but when I went to open it stopped working.

I now can’t apply the code to any spreadsheet.

How can I fix that? Code below:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Application.ScreenUpdating = False
' Clear the color of all the cells
Cells.Interior.ColorIndex = 0
If Target.Cells.Count > 1 Then Exit Sub
With Target
' Highlight the entire row and column that contain the active cell
.EntireRow.Interior.ColorIndex = 6
.EntireColumn.Interior.ColorIndex = 6
End With
Application.ScreenUpdating = True
End Sub
6 Replies

@Laura Backshell 

Did you save the file as Macro-Enabled Workbook?

Point to remember is, only .xls or .xlsb or .xlsm file can hold the macro in it.

Hi

Yeah, even when I apply to a new file or save and open as xsl etc it still doesn’t apply. Saves the code fine but doesn’t make a difference.

@Laura Backshell 

Check the attached in which I have placed the code on Sheet1 Module, let me know if that works for you.

Make sure to enable the macros in the file when prompted. If macros are disabled, they won't work.

 

Hi

The code worked fine when I used your spreadsheet, but when I opened a new excel file and added the module it didn’t work.

How do I make it active in the spreadsheet? It’s Saved as macro-enabled before I added the code but it’s not highlighting the rows.

Thanks

@Laura Backshell 

This is selection change event code which gets triggered automatically when you change the selection on the sheet and it should be placed on the Sheet Module but not on a standard module like Module1.

To place the code on to the Sheet Module, right click on the Sheet Tab Name and choose View Code and then paste the code into the opened code window, save your file as Macro-Enabled Workbook.

 

The file I uploaded with the code would work each time you open it because the code was placed on the Sheet Module.

I want my headers to stay at top of page as scrolling down. How do I do that?