Highlight Row and Column VBA

[SRMPURCHASE]

After years of successful use, once a cell is selected the row and column are highlighted, works fine until recently I have to hit F9 to enable the macro.
What would cause this F9 requirement when in the past I have not needed it, the highlighting simply followed my cell selections.


Private Sub Workbook_Open()
End Sub
Sub EnableEvents()
Application.EnableEvents = 1
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.ScreenUpdating = True
End Sub

 

[SRMPURCHASE]

This is how I have it View Code now; the EnableEvents code you say to run once is at the bottom, I'm confused at this point.
How will this code replace the manual F9 I've been pressing?

Private Sub Workbook_Open()
Call EnableEvents
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.ScreenUpdating = True
End Sub

Sub EnableEvents()
Application.EnableEvents = 1
End Sub

 

[Michael M]

Maybe this in the sheet module

Option Explicit
Const iInternational As Integer = Not (0)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iColor As Integer
On Error Resume Next
iColor = Target.Interior.ColorIndex
If iColor < 0 Then
    iColor = 36
Else
    iColor = iColor + 1
End If
If iColor = Target.Font.ColorIndex Then iColor = iColor + 1
Cells.FormatConditions.Delete
With Range("A" & Target.Row, Target.Address) 'Rows(Target.Row)
    .FormatConditions.Add Type:=2, Formula1:=iInternational 'Or just 1 '"TRUE"
    .FormatConditions(1).Interior.ColorIndex = iColor
End With
With Range(Target.Offset(1 - Target.Row, 0).Address & ":" & Target.Offset(-1, 0).Address)
    .FormatConditions.Add Type:=2, Formula1:=iInternational 'Or just 1 '"TRUE"
    .FormatConditions(1).Interior.ColorIndex = iColor
End With

End Sub

 

[SRMPURCHASE]

So, run this macro on it's own? When I try to run it on its own it goes into the rest of the script and hangs up the macro, it works, just by pressing F9 when I never had to do that.
I am somewhat proficient with VBA, mostly asking on these kind of forums to solve a problem and someone sends me the VBA, not sure why it is broke now from how it performed in the past.

 

[Michael M]

It goes in the sheet module and runs when you select a cell.
WILL overwrite CF though

 

[DanteAmor]

This is how I have it View Code now; the EnableEvents code you say to run once is at the bottom, I'm confused at this point.
How will this code replace the manual F9 I've been pressing?

Private Sub Workbook_Open()
Call EnableEvents
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.ScreenUpdating = True
End Sub

Sub EnableEvents()
Application.EnableEvents = 1
End Sub

Do you have all that code in the events of ThisWorkbook?

Verify that the code is in ThisWorkbook.
Close the book and reopen the book.


And share your book on google drive, so I can review it.

You could upload a copy of your file to a free site such www.dropbox.com or google drive.
Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.
If the workbook contains confidential information, you could replace it with generic data.

 

[jdellasala]

Last month I was trying to write a LAMBDA function that used the Excel 4.0 Macro GET.WORKBOOK(1) to generate a list of Sheet names. I had learned the technique from this video that was two years old, but Excel (365 Insider edition) kept blocking it. Eventually I found Working with Excel 4.0 macros on the Microsoft Support site which solved the problem.
Hope that helps!

 

[SRMPURCHASE]

Saving it in The Workbook worked, What resource can I turn to to learn which macros should be put in a Sheet vs. Workbook vs. Modules.
I use several macros over many workbooks, I know where they are but may need to move them around to keep everything stable.
Thanks for your patience in solving this problem for me.

 

[DanteAmor]

Saving it in The Workbook worked

I don't normally ask for my answer to be marked as a solution, but with so many answers in the thread, it would be helpful for other readers to know what the solution to this thread is, so you could mark post #15 as a solution.

What resource can I turn to to learn which macros should be put in a Sheet vs. Workbook vs. Modules

The first thing is the experience you will have by continually trying and creating macros.

But it could be:
- If you are going to fire a code when you modify or select a cell, then the code goes in the events of that sheet.
- If it's going to influence any sheet, then it goes in the events of Thisworkbook.
- If you are not going to fire an event, then the macro goes in a module.

 

[SRMPURCHASE]

# 15 noted as solution
more questions now from your reply, I only use that code in that sheet, I don't know why it didn't work in the sheet???

But, I'm glad it works in This Workbook.
I use this macro in multiple workbooks, I'll check and get this code in This Workbook.
I'll check out which workbooks have macros in a module and follow your logic then.

 

[DanteAmor]

I only use that code in that sheet

The syntax to use it only in that sheet is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Application.ScreenUpdating = True
End Sub

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.