vba - how to ignore current cell before proceeding through Loop

dynamimic

New Member
Joined
Mar 24, 2013
Messages
4
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Hi

Try:

Code:
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
 
Upvote 0
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.

Code:
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. :)
 
Upvote 0
Thanks Peter_SSs

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

Compile error: End If without block If

Thoughts?

-dynamimic
 
Upvote 0
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.
 
Upvote 0
Deleted. Sorry, I hadn't seen your latest post.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top