# Highlight Row and Column VBA



## SRMPURCHASE (Dec 15, 2022)

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


----------



## DanteAmor (Dec 15, 2022)

Run this code:


```
Sub EnableEvents()
Application.EnableEvents = 1
End Sub
```

Update this:

```
Private Sub Workbook_Open()
  Call EnableEvents
End Sub
```


----------



## SRMPURCHASE (Dec 15, 2022)

I deleted the first two lines of my code, inserted yours and I still need F9 to highlight row/col.


----------



## DanteAmor (Dec 15, 2022)

But you have to run this macro once:

```
Sub EnableEvents()
Application.EnableEvents = 1
End Sub
```

Go back to the sheet and try.
--------------------------------

Update your codes so that they look like this:


```
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
```


----------



## SRMPURCHASE (Dec 15, 2022)

I still need to press F9 to get the row/col highlight to change.


----------



## DanteAmor (Dec 15, 2022)

How is your conditional formatting?
Can you share your file?

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.*


----------



## SRMPURCHASE (Dec 15, 2022)

here's the CF behind that macro, macro range is B24:BE1953

=OR(AND(CELL("col")=CELL("col",B24),CELL("row")>CELL("row",B24)),AND(CELL("row")=CELL("row",B24),CELL("col")>CELL("col",B24)))+$A$1:$H$1


----------



## DanteAmor (Dec 15, 2022)

Works for me.

It looks to me like you haven't run this macro:

```
Sub EnableEvents()
  Application.EnableEvents = True
End Sub
```

Or 
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.*


----------



## SRMPURCHASE (Dec 15, 2022)

Here's what I have in the tab under View Code now.
Working on getting a sharable link from my One Drive, not as easy as I would like.


Sub EnableEvents()
Application.EnableEvents = 1
End Sub


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


----------



## DanteAmor (Dec 15, 2022)

I don't know how to explain it, but you have to run this macro:


```
Sub EnableEvents()
  Application.EnableEvents = True
End Sub
```

*HOW TO INSTALL MACROs*
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (*EnableEvents*) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.


----------



## SRMPURCHASE (Dec 15, 2022)

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 (Dec 15, 2022)

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 (Dec 15, 2022)

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 (Dec 15, 2022)

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 (Dec 15, 2022)

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


----------



## DanteAmor (Dec 15, 2022)

SRMPURCHASE said:


> 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()
> ...



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 (Dec 16, 2022)

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 (Dec 16, 2022)

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 (Dec 16, 2022)

SRMPURCHASE said:


> 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*.



SRMPURCHASE said:


> 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 (Dec 16, 2022)

# 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 (Dec 16, 2022)

SRMPURCHASE said:


> 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.


----------



## SRMPURCHASE (Dec 15, 2022)

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 (Dec 16, 2022)

Got it, many thanks again for your assistance, learned a lot in a short time.


----------

