Question from a rookie

[Peskarik]

Hello,
THough I have been registered here for a year, I've never really used VBA, until 2 days ago. Decided to learn a little.

background and question:
I have an open workbook with 2 worksheets: "Shading" and "main". I want to shade every second row in Shading as long as there are values in the first column (I have values 1 thru 25 in column A). The code for this I have from the VBA bible, but I wanted to play a little by running the procedure while "main" is selected. And it does not work. But if I select "Shading" and run the procedure - it works. Could someone please explain why it does not work? Is "With" statement incorrectly specified? Thanks in advance!

Module1 code:

Option Explicit

Sub ShadeEverySecondRow()
With ThisWorkbook.Worksheets("Shading")
.Range("A2").EntireRow.Select
Do While ActiveCell.Value <> ""
Selection.Interior.ColorIndex = 15
ActiveCell.Offset(2, 0).EntireRow.Select
Loop
End With
End Sub

Sergo

 

[AnotherHiggins]

No VBA required to shade alternate rows. Just use Conditional Formatting.

Select whatever cells you want to shade, then go to Format > Conditional Formatting. Change the first box to Formula Is. In the second box, type in [COLOR=blue white]=Mod(Row(),2)=0[/color] *. Select Format, go to the Patterns tab and select a fill color.

Now, you want to learn VBA. No problem - just turn on your macro recorder (Tools > Macro > Record New Macro) and do the above steps.

You can post the generated code for help understanding it and cleaning it up.


*This will shade even row numbers. For odd row numbers, just change the formula to =Mod(Row(),2)=1.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Peskarik]

Thanks, John, but the question is not so much whether VBA is required, but rather why the code does not work in this particular case. I am trying to understand how VBA functions.

Sergo

 

[SkipVought]

You cannot use the Select method on a sheet that is not active.

However, I would recommend using the Select and Activate methods as little as possible, instead, referencing the range properly...

Sub ShadeEverySecondRow()
dim r as range
With ThisWorkbook.Worksheets("Shading")

  for each r in .Range(.cells(2, "A"), .cells(.cells.rows.count, "A").end(xlup))
    if r.value <> "" then _
       r.entirerow.interior.colorindex=15
  next
End Sub

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[Peskarik]

Thanks, Skip.
I thought the problem was ActiveCell, that is, when I am in "main" sheet, active cell is in that sheet rather than in "Shading", and that was the cause of the problem.

Sergo

 

[AnotherHiggins]

If I understand, you are trying to shade even rows on sheet "Shading", but only for all populated rows on Sheet "main".

Just to offer something to play with, this will do that:

Sub shade2()
Worksheets("Main").Select
    With ThisWorkbook.Worksheets("Shading")
        For i = 2 To ActiveSheet.UsedRange.Rows.Count Step 2
            .Range("A" & i).EntireRow.Interior.ColorIndex = 15
         Next i
    End With
End Sub

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Peskarik]

John, thanks again.
I write down all suggested code snippets and take it apart using VBE help files.

Now, you misunderstood me a little. I have two sheets, the "main" sheet does not matter to me, it could be empty or full of stuff. On the "Shading" sheet, on the other hand, I have numbers 1:25 in column A, starting from A1, and I have that Sub that colors every second row of Shading, until there is no value in A. Now, I want to run the macro while having "main" sheet active, and it does not work. Skip said that Select method does not work on inactive sheets. I thought problem was with ActiveCell object.

Sergo

 

[SkipVought]

I ignored the "every second row" bit...

Sub ShadeEverySecondRow()
dim r as range
With ThisWorkbook.Worksheets("Shading")

  for each r in .Range(.cells(2, "A"), .cells(.cells.rows.count, "A").end(xlup))
    if r.value <> "" and r.row mod 2 = 0 then _
       r.entirerow.interior.colorindex=15
  next
End Sub

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}