Highlight Row AND Column of Active Cell

[HollyAnn73]

Is there a function that will allow for the active cell's row and column to be highlighted at the same time (while working)?

 

[Weaver]

If you right clck the tab, choose 'view code' and paste the following.

Dim r As Long, c As Long
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If r > 0 Then
        Rows(r).Interior.ColorIndex = xlNone
        Columns(c).Interior.ColorIndex = xlNone
    End If
    r = Target.Row
    c = Target.Column
    Rows(r).Interior.ColorIndex = 8
    Columns(c).Interior.ColorIndex = 8
End Sub

you can change the colour if you need to but it's no good if your sheet uses shading elsewhere, as it will remove it.

 

[Jaafar Tribak]

This also works for multi-cell selections:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Static lPrevRow As Long
    Static lPrevCol As Long
    Static oPrevSelection As Range

    On Error Resume Next

    oPrevSelection.EntireRow.Interior.ColorIndex = xlNone 'no fill
    oPrevSelection.EntireColumn.Interior.ColorIndex = xlNone

    Target.EntireRow.Interior.ColorIndex = 4 'green
    Target.EntireColumn.Interior.ColorIndex = 4

    Set oPrevSelection = Target

End Sub

Note that these two codes will make you lose the initial fills of cells !

You can add Conditional formatting to highlight the cells which is less likely to cause this problem but for a more robust solution that doesn't mess with the fills of your cells you will need to apply some GDI APIs.

 

[Yahya]

Salam
you can do it by Conditional Formating
1) select the table
2) form Tab Home -> Conditional Formating -> New Rule , use this formula

=OR(CELL("row")=ROW(),CELL("col")=COLUMN())

3)choose Your Formatting .
4) Right Click Sheet Name --> View Code , and put this code

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

HTH

 

[Jaafar Tribak]

Salam
you can do it by Conditional Formating
1) select the table
2) form Tab Home -> Conditional Formating -> New Rule , use this formula

=OR(CELL("row")=ROW(),CELL("col")=COLUMN())

3)choose Your Formatting .
4) Right Click Sheet Name --> View Code , and put this code

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

HTH

Salam Yahya

Nice !

 

[HollyAnn73]

I'm afraid I may have over complicated my question by not being clear. I like the answers i got an they give me ideas for something else.

but...

You know when you click on the row or column header the whole row or column selects and highlights? I want that to happen when clicking around. So if I click on a blank sheet on F7 then I will essentially have a highlighted T on my screen.

 

[Weaver]

The code we've published ought to allow that on one sheet at least (ie. the sheet you pasted the code into)

Can you confirm you're getting that result?

 

[HollyAnn73]

I don't have VBA in Mac. I was just hoping that there was a option that I missed. If I get a change I will test it out at home on my PC version. Thank you very much for your help.

 

[Weaver]

I don't have VBA in Mac. I was just hoping that there was a option that I missed. If I get a change I will test it out at home on my PC version. Thank you very much for your help.

Ah, sorry, I missed you were working on a Mac ;^(

 

[Walky]

Correction: works perfectly in Excel 2003, Yahya's instructions are simply for the layout of another version of Excel.

It's great because IT PRESERVES THE PREVIOUS FORMATTING!!!

Thx a million Yahya.

W.

Hello,

Regarding Yahya's instructions in order to enter the code below, I presume this functionality is not available in Excel 2003... could someone please confirm, as I see no form Tab to start with and no New Rule option in Conditional Formatting.

Thx,

W.

Salam
you can do it by Conditional Formating
1) select the table
2) form Tab Home -> Conditional Formating -> New Rule , use this formula

=OR(CELL("row")=ROW(),CELL("col")=COLUMN())

3)choose Your Formatting .
4) Right Click Sheet Name --> View Code , and put this code

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

HTH