• 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 row selection

inddon

Member
Hello There,

In one of my earlier post, regarding highlighting Table's current row selection was solved.
You can refer the above link for the working of a table's current row selection example.

I would like to know, how this can be worked out (VBA Code) without a table in a worksheet. Here the indicator column would be Column A.
If the current row selection has any value then:
-the indicator column A's cell should highlight a Yellow colour.
-rowheight = 25
-horizontal center aligned
-row = bold
If the current row selection has any no value then:
-it should do nothing.
When the user leaves the selection, the row should be back to it's previous state

Please find attached a sample workbook for your reference.

Thank you and look forward to hearing from you.

Regards,
Don
 

Attachments

RDAngelo

New Member
Possibly...
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If WorksheetFunction.CountA(Rows(Target.Row)) > 0 Then
        Target.CurrentRegion.ClearFormats
        Rows(Target.Row).Font.Bold = True
        Target.RowHeight = 25
        Cells(Target.Row, 1).Interior.Color = vbYellow
    End If
End Sub
 

inddon

Member
Possibly...
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If WorksheetFunction.CountA(Rows(Target.Row)) > 0 Then
        Target.CurrentRegion.ClearFormats
        Rows(Target.Row).Font.Bold = True
        Target.RowHeight = 25
        Cells(Target.Row, 1).Interior.Color = vbYellow
    End If
End Sub
Thank you very much RDAngelo for your VBA code.
There is just one catch. When I navigate to a new row, it leaves the trail behind (yellow highlighting and rowheight = 25) in the previous last row clicked. On click to a new row, the previous clicked row should get back it's formatting and rowheight? How can this be resolved?

Regards,
Don
 

RDAngelo

New Member
...If the current row selection has any no value then:
-it should do nothing...

...On click to a new row, the previous clicked row should get back it's formatting and rowheight...
Unless you pre-define the range of the table to include blank rows, your constraints are in contradiction.
 

inddon

Member
Unless you pre-define the range of the table to include blank rows, your constraints are in contradiction.

Hi RDAngelo

Thank you for your reply.

I suppose I must have mixed it up with the working of the excel table solution and made it unclear. My apologies.

I have attached a sample workbook for your reference and also how this would work.
As you mentioned about the pre-defined range: If the range can be dynamically derived as seen in the sample workbook.

It would be wonderful to see this working. Looking forward for it.

Regards,
Don
 

Attachments

RDAngelo

New Member
Try this version...
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rg As Range, c As Range
    Set rg = Sheet1.Cells(4, 1).CurrentRegion.Offset(1)
    Set c = Application.Intersect(Target, rg)
    If Not c Is Nothing Then
        rg.ClearFormats
        rg.RowHeight = 15
        Rows(Target.Row).Font.Bold = True
        Target.RowHeight = 25
        Cells(Target.Row, 1).Interior.Color = vbYellow
    Else
        rg.ClearFormats
        rg.RowHeight = 15
    End If
End Sub
 
Top