Paste then select row

[justDave]

Hi all,

Sorry for all the questions lately!....

I am using the following code (shamelessly taken from the MS site for UserForms) to copy and paste data into a worksheet:

ws.Cells(iRow, 1).Value = Me.TextBox1.Value
ws.Cells(iRow, 2).Value = Me.TextBox2.Value

What I would then like to do is select the row which the data is pasted into. I've tried ActiveCell.EntireRow.Select but this selects the row which was copied from - sorry for the basic question but could anyone tell me how to select the correct row?

Many thanks,

Dave

 

[VoG]

Maybe

Rows(iRow).Select

but why do you need to select it?

 

[DauntingGecko]

ws.Cells(iRow, 1).Value = Me.TextBox1.Value

in English means worksheet cells (row, column 1) is the same as textbox1 value on a userform

What is iRow? 1,2,3,4...?

 

[justDave]

Thank you for both of your quick replies.

I am using the UserForm methodology from this link (http://www.contextures.com/xluserform01.html) but I have changed the headings etc to suit my spreadsheet.

What I would like to do is highlight and shade the row whenever the UserForm is used paste a row into my sheet. There are only 2 'iRows': Task Number and Task Description.

 

[VoG]

Try like this (without selecting)

ws.Rows(iRow).Interior.ColorIndex=6

 

[justDave]

Many thanks VoG, that is exactly what I was after. Thank you for your help

 

[justDave]

VoG, all,

Sorry to bug you again but how would the code alter if I only want to select and colour the first 6 columns instead of the whole row?

 

[VoG]

Try

ws.Cells(iRow, 1).Resize(, 6).Interior.ColorIndex = 6

 

[justDave]

Amazing, thank you!

One final question (hopefully!) why does the Font.Bold = True function not work when I attach it to the end of that code?

 

[VoG]

This should work

With ws.Cells(iRow, 1).Resize(, 6)
    .Interior.ColorIndex = 6
    .Font.Bold = True
End With