Highlight cross section/interest of current cell in table

[gazmoz17]

Hi,

Wish to colour fill my current cell within active table.

Manged to do it via this code. But how do I automatically get this to work in any workbook/any active table? Also the current code removes existing colour fill both within the table and outside of the table. Is there a way to preserve exisitng colour fill once current cell is de-selected.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    
    Dim tbl As ListObject
    Dim tblRange As Range
    Dim tblHeader As Range

    ' Check if the selected cell is within a table
    Set tbl = Nothing
    Set tbl = ActiveSheet.ListObjects(Target.ListObject.Name)
    
    ' If a table is found, get the table range and header range
    If Not tbl Is Nothing Then
        Set tblRange = tbl.DataBodyRange
        Set tblHeader = tbl.HeaderRowRange
        
        ' Clear formatting in the entire worksheet
        Cells.Interior.ColorIndex = xlColorIndexNone
        
        ' Check if the selected cell is within the table range
        If Not Intersect(Target, tblRange) Is Nothing Then
            ' Get the entire column of the active cell
            Dim targetColumnIndex As Long
            targetColumnIndex = Target.Column - tblRange.Columns(1).Column + 1
            
            ' Check if the target column index is within the valid range of the table
            If targetColumnIndex >= 1 And targetColumnIndex <= tbl.ListColumns.Count Then
                ' Apply formatting to the active column within the table
                On Error Resume Next
                tbl.ListColumns(targetColumnIndex).DataBodyRange.Interior.ColorIndex = 37
                tbl.ListRows(Target.Row - tblRange.Rows(1).Row + 1).Range.Resize(, tbl.ListColumns.Count).Interior.ColorIndex = 37
                tblHeader.Columns(targetColumnIndex).Interior.Color = RGB(255, 165, 0)
                On Error GoTo 0
                
                ' Clear formatting for the active cell
                Target.Interior.ColorIndex = xlColorIndexNone
            End If
        End If
    End If
    
    ' Clear formatting if the selected cell is outside the table range
    If tbl Is Nothing Or Intersect(Target, tblRange) Is Nothing Then
        Cells.Interior.ColorIndex = xlColorIndexNone
    End If
    
    On Error GoTo 0
End Sub

Many Thanks
gareth

 

[DanteAmor]

Use conditional formatting

Create a new rule.

On the Home tab, in the Styles group, click Conditional formatting > New Rule… In the New Formatting Rule window, select Use a formula to determine which cells to format. Enter the formula in the corresponding box.

Excel Formula:
=OR(ROW()=CELL("row"),COLUMN()=CELL("col"))

Click the Format… button to choose your custom format.

Put the following code in the events of your sheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = True
End Sub

Review:

Select Entire Row as Default

Noob question guys. Is there a way to change the select cell default to select the entire row and make it default to do that? Would like to see the row I am currently working on which is why I am asking. I use CAD a lot and it has a crosshair that you can extend to both end so you can see...

www.mrexcel.com

How to highlight Active Cell in Excel using Conditional Formatting.

Hey Excel Gurus, I would like to know the formula for highlighting the active cell of my worksheet using Excel's built in Conditional Formatting rules. Will appreciate and thank you.

www.mrexcel.com