If Then HELP!!!
 

Dim r As Range
For Each r In ActiveSheet.UsedRange
If r.Interior.ColorIndex = 6 Then Cell.ange(Selection, Selection(30 -
Selection.Value)).EntireRow.Insert
Next

I'm not getting this right, and am unsure where to make a fix.
I want this code to select every cell that is yellow Insert rows based on
the value in the cell up to 30. So if there is a 16 it will insert 14 rows,
15 inserts 15 rows etc etc..... So what am I doing wrong with this code??

THANKS!!!!!

If Then HELP!!!
 

You have two problems. One, you have a typo at "Then Cell.ange". Secondly,
your "UsedRange" continually changes when you insert cells. When I tried
your code, it looped forever. You will need to find a way to make the code
loop through the used cells but eventually come to the end.

For example, try a Do...While loop and have the end condition include an
entirely blank or unfilled cell.

Good luck,
Pflugs

"bodhisatvaofboogie" wrote:

Dim r As Range
For Each r In ActiveSheet.UsedRange
If r.Interior.ColorIndex = 6 Then Cell.ange(Selection, Selection(30 -
Selection.Value)).EntireRow.Insert
Next

I'm not getting this right, and am unsure where to make a fix.
I want this code to select every cell that is yellow Insert rows based on
the value in the cell up to 30. So if there is a 16 it will insert 14 rows,
15 inserts 15 rows etc etc..... So what am I doing wrong with this code??

THANKS!!!!!

If Then HELP!!!
 

Yeah I caught that typo and tried it again....it does nothing for me...didn't
even loop like you said. So what would you suggest the Code look like, I'm
obiously too novice to figure it out.. :) THANKS!!!

"Pflugs" wrote:

You have two problems. One, you have a typo at "Then Cell.ange". Secondly,
your "UsedRange" continually changes when you insert cells. When I tried
your code, it looped forever. You will need to find a way to make the code
loop through the used cells but eventually come to the end.

For example, try a Do...While loop and have the end condition include an
entirely blank or unfilled cell.

Good luck,
Pflugs

"bodhisatvaofboogie" wrote:

Dim r As Range
For Each r In ActiveSheet.UsedRange
If r.Interior.ColorIndex = 6 Then Cell.ange(Selection, Selection(30 -
Selection.Value)).EntireRow.Insert
Next

I'm not getting this right, and am unsure where to make a fix.
I want this code to select every cell that is yellow Insert rows based on
the value in the cell up to 30. So if there is a 16 it will insert 14 rows,
15 inserts 15 rows etc etc..... So what am I doing wrong with this code??

THANKS!!!!!

If Then HELP!!!
 

hi,
using UsedRange will keep you going forever for as you as rows, you add range.
ange???? i think this is suppose to be Range.
selection??? your not selecting anything.
try this
Sub MacAddRows()
Dim r As Range
Dim r1 As Range
Dim lr As Range

Set r = Range("A1")
Set lr = Range("a65000").End(xlUp)
'MsgBox lr.Address ' for debug perposes only
Do While r.Address < lr.Address
Set r1 = r.Offset(1, 0)
If r.Interior.ColorIndex = 6 Then
Range(r1, r1(30 - r.Value)).EntireRow.Insert
Set r1 = r.Offset(1, 0)
Range(r1, r1(30 - r.Value)).Interior.ColorIndex = 0
End If
Set r = r1
'MsgBox r.Address 'for debug purposes only
Loop

End Sub
regards
FSt1

"bodhisatvaofboogie" wrote:

Dim r As Range
For Each r In ActiveSheet.UsedRange
If r.Interior.ColorIndex = 6 Then Cell.ange(Selection, Selection(30 -
Selection.Value)).EntireRow.Insert
Next

I'm not getting this right, and am unsure where to make a fix.
I want this code to select every cell that is yellow Insert rows based on
the value in the cell up to 30. So if there is a 16 it will insert 14 rows,
15 inserts 15 rows etc etc..... So what am I doing wrong with this code??

THANKS!!!!!