How to highlight active row and column with vba code?

Question

Hi,

How to use default highlight effect to highlight active row and column with vba code within tables on different worksheets?

Only available for specified table for multiple worksheets.

Many thanks

J.

Answer 1

Hi J

Try this code for the Worksheet_SelectionChange event

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.ScreenUpdating = False 

Cells.Interior.ColorIndex = 0 

Target.EntireColumn.Interior.ColorIndex = 6 

Target.EntireRow.Interior.ColorIndex = 6 

Application.ScreenUpdating = True 

End Sub

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Regards

Jeovany

Answer 2

Hi,

Thank you for your help.

Currently, this effect can only run in sheet1. Is there a way to apply to all worksheets and run within the specified sheet? as shown below:

The default color is RGB (191，191，191), semi-transparent. If possible, I would like to use TOGGLE to run or stop to highlight by one click and also without affecting other conditional formatting on the same worksheet.

Is there any way to achieve this function please？

Many thanks

J.

Answer 3

OK

Then delete the previous code from the Sheet1 event and paste the code below for the Workbook event as indicated in the picture

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

Dim myRange As Range

Set myRange = ActiveSheet.UsedRange

Rw = myRange.Rows(1).Row

Cl = myRange.Columns(1).Column

Application.ScreenUpdating = False

ActiveSheet.Cells.Interior.ColorIndex = 0

If Not Application.Intersect(Target, myRange) Is Nothing Then

    myRange.Columns(Target.Column - Cl + 1).Interior.Color = RGB(191, 191, 191) 

    myRange.Rows(Target.Row - Rw + 1).Interior.Color = RGB(191, 191, 191) 

End If

Application.ScreenUpdating = True

End Sub

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Regards

Jeovany

Answer 4

Hi Jeovany

Thank you for you code and also saved as macro-enabled spreadsheet, but it still doesn't give any response.

Where did I go wrong please?

J.

Answer 5

You may find and download from the link below a file with the macro-code and check by yourself how it works.

The workbook has 4 sheets with different size table ranges.

https://we.tl/t-LVKz6atgqi

Do let me know how it goes