start macro with data in input box

[still learning]

hi
This works:

Sub startbox()
Dim i As Long
n = InputBox("                   Where Should the Fill Color Start?")
x = InputBox("                   How many times to run?")
Range("B" & n).Select
    For i = 1 To x
    With Range("A1:I1").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 16645315
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Next i
End Sub

This doesn't. It only runs once.

Sub unfillnew()
Dim i As Long
n = InputBox("                   Where does the Un fill Start?")
x = InputBox("                   How many times to run?")
Range("B" & n).Select
    For i = 1 To x
   ActiveCell.Range("A1:I1").Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Next i
    ActiveCell.Select
End Sub

I can't see what I'm doing wrong


The top one changes the fill of the row the user puts in the input box and does it x number of times
I'm trying to have a macro change the row back to "no fill" base on the user's input. start and x times.

Being a "still learning", I thought I could easily just change some of the working macro to suit the new conditions.
I had to add >>ActiveCell.Select << in the second to get the cursor back to the starting point (Range("B" & n).Select)
the first one didn't need it.
Also. If I want to combine the two can I just change the N and X of the second one?????

mike

 

[Fluff]

You're first code will put the fill on range A1:I1, regardless of what the user puts in the 2 input boxes.

 

[still learning]

Hi Fluff
When I run this (some of this I got with help here. some with google)

Sub startbox()
Dim i As Long
n = InputBox("                   Where Should the Fill Color Start?")
x = InputBox("                   How many times to run?")
Range("B" & n).Select
    For i = 1 To x
    With Range("A1:I1").Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 16645315
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Next i
End Sub

I put in a number in the first box to where I want the fill to start, then the second box tells how many times to run it
I've used different combination of numbers and it works fine (so Far)


I thought I could just change the >>with range>> and the words of the input boxes.
The first part works >>where to start, but it only runs once no matter what was put in the second box

The sheet has every other row filled with an interior color from B to I
When I sort, the fill doesn't go with the data.
The first macro (startbox) corrects that
Sometimes I have to manually >>fil..no color>> some rows because of where the sort ended up. I am trying to make a macro for that.
I tried to put in a fixed starting point and range, but I would have to change that as data is added.

mike

 

[Fluff]

In that case there is something else going on, because you have hardcoded this range

 With Range("A1:I1").Interior

so they should be the only cellls getting filled

 

[still learning]

boy am I stupid and embarrassed

I was using a macro to sort then it would call the startbox macro. I didn't realize I had 2 startbox macros and it would run this one. The one that I was trying to modify was the other one.
I guess I couldn't get the other to work and came up with this one. I have a habit of not deleting. And at my age, not remembering.

Sub startbox()
Dim i As Long
n = InputBox("                Where Should the Fill Color Start?")
x = InputBox("                How many times to run?")
Range("B" & n).Select
    For i = 1 To x
        Call forfillingcolor
    Next i
End Sub

the macro >>forfillingcolor is

Sub forfillingcolor1()
    ActiveCell.Range("A1:I1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 16645315
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    ActiveCell.Offset(2, 0).Range("A1").Select
End Sub

I changed the input boxes and the call macro
the call macro only runs once not x times

Sub startbox2()
Dim i As Long
n = InputBox("                   Where Should the UnFill Color Start?")
x = InputBox("                   How many times to run?")
Range("B" & n).Select
    For i = 1 To x
       Call unfil
    Next i
End Sub

and

Sub unfil()
    ActiveCell.Range("A1:I1").Select
    With Selection.Interior
        .Pattern = alnone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    ActiveCell.Range("A1").Select
End Sub

I tried calling unfil using the top startbox macro and still it only runs once
I went thru all the macros for the sheet to make sure I didn't have any with the same name.

Thanks for your patients


mike

 

[Fluff]

Try

ActiveCell.Offset(-2).Range("A1").Select

 

[still learning]

yep I see my error it kept running the macro on the same line. DUH.

Thank you for your help.

And your patience

Mike

 

[Fluff]

Glad to help & thanks for the feedback