Expand Range

W

WStoreyII

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
 
S

Stan Scott

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
 
Top