Call a range with a Row that is a variable

[riedyp]

Hello,

I want to call a Range that will be in my variable row "LastRow" and span from column B to column I. I have tried several different methods and cant seem to get the syntax correct. Attached is my last "hail mary" of an attempt that ultimately failed. Thank you.

Private Sub CommandButton1_Click()
LastRow = Sheet5.Cells(1000, 2).End(xlUp).Row + 2 'Finds the last edited row and then adds two rows
LastNumber = Sheet5.Cells(1000, 1).End(xlUp).Value + 1 'Finds the last question number and adds 1
 Sheet5.Cells(LastRow, 1).Select ' Selects LastRow in the first Column
    ActiveCell.Formula = LastNumber ' Gives it LastNumber
Sheet5.Range(BLastRow,ILastRow)
End Sub

 

[Fluff]

What do you want to do with the range?

 

[riedyp]

I am going to merge these cells and give them a new color.

 

[Fluff]

I would strongly recommend against merging them. Merged cells are an abomination & should be avoided like the plague.
They will only cause you problems later on.
This will change the fill colour

Private Sub CommandButton1_Click()
   LastRow = Sheet5.Cells(1000, 2).End(xlUp).Row + 2 'Finds the last edited row and then adds two rows
   LastNumber = Sheet5.Cells(1000, 1).End(xlUp).Value + 1 'Finds the last question number and adds 1
   Sheet5.Cells(LastRow, 1).Value = LastNumber ' Gives it LastNumber
   Sheet5.Range("B" & LastRow).Resize(, 8).Interior.Color = 45678
End Sub

 

[riedyp]

I would strongly recommend against merging them. Merged cells are an abomination & should be avoided like the plague.
They will only cause you problems later on.
This will change the fill colour

Private Sub CommandButton1_Click()
   LastRow = Sheet5.Cells(1000, 2).End(xlUp).Row + 2 'Finds the last edited row and then adds two rows
   LastNumber = Sheet5.Cells(1000, 1).End(xlUp).Value + 1 'Finds the last question number and adds 1
   Sheet5.Cells(LastRow, 1).Value = LastNumber ' Gives it LastNumber
   Sheet5.Range("B" & LastRow).Resize(, 8).Interior.Color = 45678
End Sub

Haha alright I will try to stay away from it, I can definitely see how it could become an annoyance. Your solution worked, Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.