Select Table Row 1

[MattGreer]

I have a table, "Table8" as it's named, in Excel 2013. I have a spreadsheet where I add daily to-do items where I sort, mark completed, and so forth using a few buttons. I wanted to add a button to highlight an individual table row to indicate that I've worked on that task today and there's nothing more to do.

So, for example, I'll click on the first cell in the row, in Column "A", and then I would click the button and the row would get shaded.

This code works but I'm sure there's a better way to do it, plus, if I have more cells filled in I'm concerned that I'll end up highlighting the whole entire excel worksheet row. I know how to select a whole table to clear the formatting; how do you select just one row?

Private Sub CommandButton3_Click()

    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorLight1
            .TintAndShade = 0.349986266670736
            .PatternTintAndShade = 0
        End With

End Sub

Thanks!!


Matt

 

[SkipVought]

Hi,

It is a best practice in Excel to use DATA to indicate a status, that can be used as a criteria for the Conditional Format feature. If you have a data element(s), they can be used without any VBA code.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[MattGreer]

I probably misunderstand, but if I *do* understand, the issue is there's no way for the spreadsheet to know I've worked on something. As I go through the list, yeah, I've worked on that, it still has a priority but I don't need to look at it again today. Tomorrow, I'll reset the spreadsheet, look through my to-do list, and figure out what needs to be worked on.

I've had such (comprehension) issues with conditional formatting. It's such a pain for me, for whatever reason, and I find VBA to be a lot easier to use, heh.

I *suppose*, I could simply enter a "worked on today" entry in one of the table columns, and then the row would format itself based on that cell. Is that sort of what you were talking about?

edit: But then, how do I reset the conditional formatting tomorrow with just one click? I'd have to parse through a column, delete/reset the formatting on only rows that contain that particuar string. That seems more complicated than my original though, but I'm willing to learn. What would you suggest?

Thanks!!


Matt

 

[MattGreer]

I fixed it doing this:

Range(Selection, ActiveCell.Offset(0, 5)).Select

Since the table will always be the same width, no problems.

I just hoped there was a nifty "range" method of some kind that is specific to tables that would not need a hard-coded number.

Thanks!!


Matt

 

[SkipVought]

With Selection
   With Intersect(.EntireRow, .CurrentRegion).Interior
       .Pattern = xlSolid
       .PatternColorIndex = xlAutomatic
       .ThemeColor = xlThemeColorLight1
       .TintAndShade = 0.349986266670736
       .PatternTintAndShade = 0
        
   End With
End With

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[MattGreer]

Geez Skip, you're amazing.

Thanks!!


Matt