Highlight Row and Column when Clicked in Excel | VBA Shortcut

⚡Access FREE Cheat Sheet: https://www.excelpowerup.com/cheatsheet
💡Learn the first steps to optimize any spreadsheet: https://www.excelpowerup.com/training
🚀Take your Excel spreadsheets to the next level: https://www.excelpowerup.com/course


This auto-highlighting feature in Excel can be implemented in a straightforward two-step process. This feature is particularly beneficial when dealing with extensive tables that may otherwise be challenging to navigate. By incorporating auto-highlighting, your spreadsheet will not only become more user-friendly but also aesthetically pleasing, thereby enhancing the overall user experience.


VBA Code
-------------------------------------------------------------------------------
‘ Place this code on each worksheet page than needs the auto-highlighting feature
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.Calculate
End Sub

‘ Place this code on the ThisWorkbook page or any Module page
‘Run it one time for each worksheet
Sub Highlight_Active_Cell()

' Select the table that is on the sheet
ActiveSheet.ListObjects(1).DataBodyRange.Select

' Add Row and Column highlighting
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=OR(ROW()=CELL(""row""),COLUMN()=CELL(""col""))"
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.75
End With
Selection.FormatConditions(1).StopIfTrue = False

' Add Cell highlighting
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(ROW()=CELL(""row""),COLUMN()=CELL(""col""))"
Selection.FormatConditions(2).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = -0.25
End With
Selection.FormatConditions(1).StopIfTrue = False

End Sub
-------------------------------------------------------------------------------


Website: https://www.excelpowerup.com
Instagram:   / rebekahoster  
TikTok:   / rebekahoster  
LinkedIn:   / rebekah-oster  

00:00 Introduction
00:44 The Functions
02:27 Conditional Formatting Active Row
04:02 Conditional Formatting Active Column
05:03 Create Crosshairs by Highlighting Cell
06:10 Autocalculate with VBA
08:02 Apply Feature to Multiple Tables