How to decrement row component of range variable

[Special K]

I have the following code in Excel 2010:

Option Explicit

Dim active_row As Range

Private Sub CommandButton1_Click()

Set active_row = Sheets("active").Range("A2:L2")
active_row.Interior.Color = RGB(0, 255, 0)

End Sub

Private Sub CommandButton2_Click()

active_row.Interior.Color = RGB(255, 255, 255)
; code to move active_row range down one row
active_row.Interior.Color = RGB(0, 255, 0)

End Sub

Pressing Button 1 highlights row A2:L2 light green. Each subsequent press of Button 2 is supposed to clear the current row's green highlight and then apply the same highlight to the next row down. The problem is I can't figure out how to decrement the row component of the active_row variable.

I know you can't modify the Range.Row() property of a fixed range, but isn't it possible to decrement the row component of my active_row variable by 1? I also read about the resize() function but that doesn't work either because I only want to move the highlighted region down 1 row.

Can I only do this by saving integers corresponding to the range along with the Cells() function and a for each loop?

 

[My Aswer Is This]

Would you please tell me what your trying to do.
1.Do you have a sheet named active or are you referring to the active sheet.
The active sheet is the sheet you are now working on and normally is not named active
The active sheet can be named anything.

2. Are you wanting to enter data onto a row an then have that row colored green and then next time you enter data on the next row have that row turn green and the previous row turn back to white or as excel looks at it it's called xlnone

3. I really do not know why you think you need two buttons to do this.
4. And we want this color from columns A to L

And you know this could be done automatically when you enter data into column A if you wanted with no need for a button.

 

[RoryA]

Do you mean:

set active_row = active_row.offset(1)

 

[Special K]

Would you please tell me what your trying to do.
1.Do you have a sheet named active or are you referring to the active sheet.
The active sheet is the sheet you are now working on and normally is not named active
The active sheet can be named anything.

I have a sheet named "active". Perhaps this is misleading given that "active" has a special meaning in Excel. I would update the OP but apparently the edit functionality expires after a period of time, as I don't see an edit button on my post.

2. Are you wanting to enter data onto a row an then have that row colored green and then next time you enter data on the next row have that row turn green and the previous row turn back to white or as excel looks at it it's called xlnone

button2 is used to cycle through a list of rows, highlighting the current row and performing a list of actions with each button press. Currently only the highlight feature is implemented.

button1 is used to reset the state of the spreadsheet and move the highlight back to the second row.

3. I really do not know why you think you need two buttons to do this.

See my answer to question 2. The functionality has not been added yet. Right now I'm only trying to get the highlight working.

4. And we want this color from columns A to L

And you know this could be done automatically when you enter data into column A if you wanted with no need for a button.

button1 will eventually be used to perform a list of actions with each press, in addition to moving the highlight down one row. This has not been added yet. button2 is effectively a "reset" button to remove all the modifications performed by repeatedly pressing button1.

 

[Special K]

Do you mean:

set active_row = active_row.offset(1)

Yes, this is exactly what I needed. Thanks. I actually ended up using:

Set active_row = active_row.Offset(1,0)

Because it says Offset has 2 required arguments.

 

[Rick Rothstein]

I have the following code in Excel 2010:

Option Explicit

Dim active_row As Range

Private Sub CommandButton1_Click()

Set active_row = Sheets("active").Range("A2:L2")
active_row.Interior.Color = RGB(0, 255, 0)

End Sub

Private Sub CommandButton2_Click()

active_row.Interior.Color = RGB(255, 255, 255)
; code to move active_row range down one row
active_row.Interior.Color = RGB(0, 255, 0)

End Sub

Pressing Button 1 highlights row A2:L2 light green. Each subsequent press of Button 2 is supposed to clear the current row's green highlight and then apply the same highlight to the next row down. The problem is I can't figure out how to decrement the row component of the active_row variable.

I know you can't modify the Range.Row() property of a fixed range, but isn't it possible to decrement the row component of my active_row variable by 1? I also read about the resize() function but that doesn't work either because I only want to move the highlighted region down 1 row.

Can I only do this by saving integers corresponding to the range along with the Cells() function and a for each loop?

Try it like this...

Dim active_row As Range

Private Sub CommandButton1_Click()
  Set active_row = Sheets("active").Range("A2:L2")
  active_row.Interior.Color = RGB(0, 255, 0)
End Sub

Private Sub CommandButton2_Click()
  active_row.Interior.Color = xlNone
  Set active_row = active_row.Offset(1)
  active_row.Interior.Color = RGB(0, 255, 0)
End Sub

Note: The physical ActiveCell never changes with this coding (not sure if that is okay with you or not, but your above originally posted code did not affect the ActiveCell, so I continued with that).