• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Highlight Current Table Row Selection (Specific Column/Entire row)

I

inddon

Member
Hello There,

I have 2 requirements to indicate current row selection in a table as below and to achieve this using VBA:
1. To highlight the current row's specific column (with fill colour)
2. To highlight the entire row (with fill colour)

I have attached a sample workbook for your reference.

Look forward to hearing from you.

Regards,
Don
 

Attachments

  • Table Indicator Sample.xlsm
    17.2 KB · Views: 5
Personally, I'd not recommend doing this sort of thing via code. It just adds fluff layer, with no significant benefit.

Excel natively gives Column & Row indicator in "Headings" section. As well as showing indicator for selected cell.

At any rate, it isn't clear to me, what logic dictates which of A / B logic should be applied...
 
Chihiro said:
.
At any rate, it isn't clear to me, what logic dictates which of A / B logic should be applied...
Click to expand...
Hi Chihiro,

These are 2 seperate requirements. The common thing in these, is the visual identification of the cursor location in the table row. I should have created 2 buttons to make it more clearer.
Case A: To highlight, only the first cell of the first column, current cursor location of the row.
Case B: To highlight the entire current cursor location of the row.

Also, curious to know about "Excel natively gives Column & Row indicator....."

Thanks,
Don
 
Chihiro said:
So you use buttons to trigger code? Doesn't it defeat the purpose of highlighting automation?

What I mean by native indicator... You see where column G & Row 5 headings are marked with darker color? This in combination with Freeze Panes tool should be enough to help in navigation.

View attachment 70484

If you must, see link below for simplest form of highlighting based on selection.
https://www.exceltip.com/events-in-vba/simplest-vba-code-to-highlight-current-row-and-column.html
Click to expand...

Hi Chihiro,

Thank you for the reference link, it is of help.
The buttons to trigger the code is only for the purpose of demo and not included in the actual scenario.

Could you please assist me in vba code with Case A, to highlight only the first column cell of the current row in table?


Regards,
Don
 
inddon
Here is a sample code for both cases.
You can change 'case' by click 'Indicator'-cell (left top of table).
Not tested with all even [im]possible variations.
 

Attachments

  • Table Indicator Sample.xlsm
    19.4 KB · Views: 17
vletm said:
inddon
Here is a sample code for both cases.
You can change 'case' by click 'Indicator'-cell (left top of table).
Not tested with all even [im]possible variations.
Click to expand...
Hi vletm

Could you please let me knowhow this is achieved?

I am not able to se any code in VBA.

Regards,
Don
 
Hello vletm

Could you help in getting your solution (of table row 'indicator' as seen in the snapshot) work for normal range (without table) in a worksheet?

71025

In the normal range (without table), the indicator column would be coloured in Column A. If the current row selection has any value then the indicator should work. If there are no values in the current row selection then it should do nothing


Thank you and look forward to hearing from you.

Regards,
Don
 
inddon
Isn't Your original thread still: Highlight Current Table Row Selection?
... table ... table ... table ...
The table ... is the table!
... and now ... You seems to throw away that 'table' ...hmm?
My solution works with ... table, as You have written.

Based Forum Rules:

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
Start a new post every time you ask a question, even if the theme is similar.
Note: Even 'minor' change of idea ... would mean to start from zero!
 
Hi vletm

Your original solution works perfectly fine. Would like to know how this would work without a table as well.
Thank you for the Site Rules, I will do a new post.

Regards,
Don
 
vletm said:
inddon
Here is a sample code for both cases.
You can change 'case' by click 'Indicator'-cell (left top of table).
Not tested with all even [im]possible variations.
Click to expand...

Hello @vletm

I have an excel requirement for table row indicator, and I remembered you had provided the solution (wow it has been 2 years) which I am using now. However, I face a minor difficulty. When the table starts from A1, the indicator current row does not work. If I include a blank column before the table it works fine.

In VBA:
the control navigates to code With ActiveSheet.ListObjects(tb), when it executes mo_de = .DataBodyRange(0, 0).Locked it goes to NoTable exception handler.

Attached snapshot of the scenario for your reference.

Please advice. Thank you and look forward to hearing from you.




Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    With ActiveCell
        yA = .Row + 1
        xA = .Column + 1
    End With
    tb = ActiveCell.ListObject.Name
    On Error GoTo NoTable
        With ActiveSheet.ListObjects(tb)
            mo_de = .DataBodyRange(0, 0).Locked
            With .DataBodyRange
                .Interior.ColorIndex = xlNone
                .RowHeight = 15
            End With
            L = .DataBodyRange(0, 0).Locked
            yT = .DataBodyRange.Row
            xT = .DataBodyRange.Column
            y = yA - yT
            x = xA - xT
            If y > 0 Then
                .DataBodyRange(y, 1).RowHeight = 22
                If mo_de Then
                    .DataBodyRange(y, 1).Interior.ColorIndex = 6
                Else
                    .ListRows(y).Range.Interior.ColorIndex = 6
                End If
            End If
            If y = 0 And x = 1 Then
                With .DataBodyRange(0, 0)
                    .Locked = Not .Locked
                    fc = .Font.ColorIndex
                    If fc = 2 Then
                        fc = 3
                    Else
                        fc = 2
                    End If
                    .Font.ColorIndex = fc
                End With
            End If
        End With
       
    Exit Sub
NoTable:
    x = x
End Sub


Regards,
Don
 

Attachments

  • Table Row Highlight.jpg
    Table Row Highlight.jpg
    76.9 KB · Views: 6
inddon
Your However, I face a minor difficulty.
Why You have made that a minor difficulty Yourself?
Why You have moved table in different position?
I would normally recommend to leave there ten empty columns (for spare).
As You have notice Yourself, leave empty A-column there - and it works!
... if that empty column is a challenge to see - then You could hide that column.
... as well as You could hide those column & row headers
 
vletm said:
inddon
Your However, I face a minor difficulty.
Why You have made that a minor difficulty Yourself?
Why You have moved table in different position?
I would normally recommend to leave there ten empty columns (for spare).
As You have notice Yourself, leave empty A-column there - and it works!
... if that empty column is a challenge to see - then You could hide that column.
... as well as You could hide those column & row headers
Click to expand...

Hi @vletm

Thank you for your reply.

The intention was to keep things as is. With the new requirement, the position of the table is changed. With this change then I remembered you and your solution you gave me :)

Unfortunately, leaving empty columns/rows before the table is not possible for the user, therefore the request if the code could be adjusted that it works irrespective of the position of the table. Hope it would be possible

Thank you again for your time.

Regards,
Don
 
inddon
Did You read my last two lines?
... is not possible for the user - - - hmm?
This kind of ... is like 8hrs fee ... with the new requirement - of course, those remember write that there too.
It would be possible.
 
vletm said:
inddon
Did You read my last two lines?
... is not possible for the user - - - hmm?
This kind of ... is like 8hrs fee ... with the new requirement - of course, those remember write that there too.
It would be possible.
Click to expand...

Hi @vletm,

Yes, I read your last post. That is why the reply.

I don't get paid anything for the work I do. I am sure they would not pay for the 8 hrs fee as well. What I know is, I can buy you a couple of beers. :)

But if the change is too much of an effort, then I let it rest.

Thank you & regards,
Don
 
You must log in or register to reply here.
Back
Top