Highlight Row and Column when Clicked in Excel | VBA Shortcut
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
Get personalized professional support: https://www.excelpowerup.com/support
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
