Learn the first steps to optimize any spreadsheet: https://www.excelpowerup.com/training'>https://www.excelpowerup.com/training
Take your Excel spreadsheets to the next level: https://www.excelpowerup.com/course'>https://www.excelpowerup.com/course
Get personalized professional support: https://www.excelpowerup.com/support'>https://www.excelpowerup.com/support
This autohighlighting feature in Excel can be implemented in a straightforward twostep process. This feature is particularly beneficial when dealing with extensive tables that may otherwise be challenging to navigate. By incorporating autohighlighting, your spreadsheet will not only become more userfriendly but also aesthetically pleasing, thereby enhancing the overall user experience.
VBA Code
‘ Place this code on each worksheet page than needs the autohighlighting 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: / rebekahoster
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