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: https://www.instagram.com/rebekahoster
TikTok: https://www.tiktok.com/@rebekahoster
LinkedIn: https://www.linkedin.com/in/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

Тэги:

#excel_cell_color #excel_change_color #excel_formula #excel_functions #excel_hacks #excel_tips #excel_tips_and_tricks #excel_tricks #excel_tutorial #formula #function #highlight #highlight_cell #highlight_column #highlight_row #how_to #how_to_excel #microsoft_excel #spreadsheet #step_by_step #tricks_in_excel #vba

Скачать видео