Expand Range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I Am Making a Ledger Program,

I have the Ledger Set up Right now with the first row selected and set up as a nemed range name Ledger.

What i want is that when the user clicks a button,
it will create a new row and add it to the sheet as well as the range directly below the first ledger. It will be an alternating color, grey /blue ect

How Can i Do this?

I have tried a hundered different things but i always get this error.

object variable or with block variable not set
 
WStoreyII,

Something like this will do the trick. As you said, the initial range
definition for Ledger is "Sheet1!$1:$1":

Sub CreateNewLedgerRow()
ActiveWorkbook.Names.Add "Ledger", "=Sheet1!" &
Range("Ledger").Offset(1).Address
With Range("Ledger")
If .Offset(-1).Interior.ColorIndex = 36 Then
.Interior.ColorIndex = 35
Else
.Interior.ColorIndex = 36
End If
End With
End Sub

The use of a single-row range makes this easy. If you want to set the
entire range as "Ledger", expand it to include the rows above it, before you
start processing.

Hope this helps,

Stan Scott
New York City

WStoreyII said:
Hello,

I Am Making a Ledger Program,

I have the Ledger Set up Right now with the first row selected and set up as a nemed range name Ledger.

What i want is that when the user clicks a button,
it will create a new row and add it to the sheet as well as the range
Click to expand...
directly below the first ledger. It will be an alternating color, grey /blue
ect
 
If your range is held within a variable, it will not be saved.

But, I managed to get around this. Put your variable in an empty cell
of the spreadsheet, and then make your sub look at this cell at the
begining and reassign the value of the cell back to the variable.
 
Back
Top