vba - how to ignore current cell before proceeding through Loop

[dynamimic]

Hi there,

vba virgin up until last week, but I'm trying to teach myself, so please go easy on me.

I'm trying to create an Excel loop which will color rows blue meeting a certain criteria. My subroutine works, however, it always first colors the currently active cell blue before running through the subroutine. I'm assuming its doing this because it's looking for the first command, which is "color selection blue", before it then runs through the loop. So how do I get it to ignore the currently active cell and only color those meeting the criteria?

Nothing too elaborate please... I'm just trying to grasp this particular concept at the moment, and am looking for a simple solution. Here is my subroutine:

Sub ColorCells()
Dim Bazinga As Integer
Bazinga = 2
Do
If Range("A" & Bazinga).Value > 6 And Range("B" & Bazinga).Value = 2 _
Or Range("A" & Bazinga).Value < 3 And Range("B" & Bazinga).Value = 2 _
Then Range("B" & Bazinga).EntireRow.Select
With Selection.Interior
.Color = 15773696
End With
Bazinga = Bazinga + 1
Loop Until Range("A" & Bazinga).Value = ""
End Sub

Thanks for your help.

-dynamimic

 

[Firefly2012]

Hi

Try:

Sub ColorCells()
Dim r As Range
For Each r in Range("A2:A" & Cells(Rows.Count,"A").End(xlUp).Row)
  If r.Offset(,1).Value > 2 Then
     If r.Value > 6 Or r.Value < 3 Then r.EntireRow.Interior.Color = 15773696
  End If
Next r
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

When you you have an IF statements and there is more than one thing to do if it is true, the code lines to do thos actions should start on a new line of code.
That is, you need a carriage return after the "Then"
As a consequence, you also need an "End If"

Indenting your code so you can see where blocks start/end and posting your code with Code Tags (see my signature block) will make things like that easier to see.

Sub ColorCells()
  Dim Bazinga As Integer
  
  Bazinga = 2
  Do
    If Range("A" & Bazinga).Value > 6 And Range("B" & Bazinga).Value = 2 _
          Or Range("A" & Bazinga).Value < 3 And Range("B" & Bazinga).Value = 2 Then
      Range("B" & Bazinga).EntireRow.Select
      With Selection.Interior
        .Color = 15773696
      End With
    End If
    Bazinga = Bazinga + 1
  Loop Until Range("A" & Bazinga).Value = ""
End Sub

I would normally have a couple of other suggestions about your code, but you said to keep it simple so I have only addressed your direct question.

 

[dynamimic]

Thanks Peter_SSs

I had tried that before, but I get the following error:

Compile error: End If without block If

Thoughts?

-dynamimic

 

[dynamimic]

Hi Firefly,

While I appreciate this would probably work far better, I'm not simply looking for a 'fix' to the problem. I'm trying to learn, so I'm looking to understand the reason for the problem and what the simplest solution would be within the context of what I currently have. A complete re-write without understanding won't help me. I'm aware my code is amateur, but I'm brand new to this.

 

[dynamimic]

No wait! You're right. I hadn't amended the "Then".
This works now. Thank you!

 

[Peter_SSs]

Deleted. Sorry, I hadn't seen your latest post.