INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Select Table Row

Select Table Row

(OP)
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?

CODE --> VBA

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

RE: Select Table Row

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Select Table Row

(OP)
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

RE: Select Table Row

(OP)
I fixed it doing this:

CODE --> VBA

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

RE: Select Table Row

CODE

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Select Table Row

(OP)
Geez Skip, you're amazing. :)

Thanks!!


Matt

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close