Help with Hightlighting the Active Row - How to apply code to all new sheets created

[kellman]

I came across this code that works however, I could really use your help to tweak-it a little for my needs.

I would like this code to run on every new sheet that gets created within my workbook automatically, rather than me pasting this code manually into each sheet as it gets created.

There must be a more elegant solution no doubt.

From what I've read the solution has to do with adding the code to a Module.

I'm a newbie to VBA Coding and would very much appreciate your assistance in creating a solution that works for my needs.

Note - is there a way to exclude the 1st Row from the coding as the 1st Row are Column descriptions only.



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False

'Clear the color of all cells
Cells.Interior.ColorIndex = 0
With Target
'Highlight row and column of the selected cell
.EntireRow.Interior.ColorIndex = 38
End With

Application.ScreenUpdating = True
End Sub

 

[6StringJazzer]

You can add similar code to the ThisWorkbook module. This code will detect the SelectionChange event in any sheet. Minimal changes are needed, see red. No code needs to be put in any sheet module.

I suggest you use code tags to paste code to preserve readability.

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

   If Target.Cells.Count > 1 Then Exit Sub
  
   Application.ScreenUpdating = False
  
   'Clear the color of all cells
   Sh.Cells.Interior.ColorIndex = 0
   With Target
      'Highlight row and column of the selected cell
      .EntireRow.Interior.ColorIndex = 38
   End With
  
   Application.ScreenUpdating = True

End Sub

BTW note that your code will remove any fill color that was applied before the row was selected. Another way to do this is to create a conditional formatting rule that references a named range, and have the code update the named cell to the range that was just selected.

 

[kellman]

Thank you Jeff, that works perfectly!

As for your suggestion to "create a conditional formatting rule that references a name range, and have the code update the named cell to the range that was just selected."

I have 9 columns from A to I on the first Row - could you give me a hint on how I would go about doing this?

 

[6StringJazzer]

The conditional formatting rule would have to applied individually to each sheet so that might not work for you.

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

   Sh.Calculate

End Sub

highlight row and column of currently selected cell.xlsm
	A	B	C	D	E	F	G	H
1	Test	Test	Test	Test	Test	Test	Test	Test
2	Test	Test	Test	Test	Test	Test	Test	Test
3	Test	Test	Test	Test	Test	Test	Test	Test
4	Test	Test	Test	Test	Test	Test	Test	Test
5	Test	Test	Test	Test	Test	Test	Test	Test
6	Test	Test	Test	Test	Test	Test	Test	Test
Row Only

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:H14	Expression	=CELL("row")=CELL("row",A1)	text	NO

With other formatting undisturbed:

 

[6StringJazzer]

Also note that the code forces a recalculation on every selection change. This could cause a performance lag in a large, complex worksheet with a lot of formulas.

 

[kellman]

Jeff - thank you for providing such great ideas to this VBA newbie.