Basic For Next

[lrubin28]

Hi - I'm trying to do what I think is a basic for-next loop. I have a range that can vary but always begins in cell B11. I want to evaluate each cell in column B and shade the row of data based on the first 2 letters of what I'm using as an identifier. For example in column B I have BU1, BU2, BU3...AL1, AL2, AL3 etc. In this example I'm just trying to get the "BU" piece to work - from there I can hopefully figure out the rest. It's hard to look at without color coding. I tried conditional formatting, but for some reason, something keeps changing the range of cells. Anyway here's what I have....(and yes, I'm a noob). Thank You!

Sub Shading()
Dim PPP As Integer
Dim NoRows As Integer
Dim i As Integer


Sheets("Filter").Range("B11").Select
Selection.End(xlDown).Select
PPP = ActiveCell.Row 'To get the last row
NoRows = PPP - 11 'To figure out how many rows there are in my data


For i = 1 To NoRows
Set curCell = Worksheets("Filter").Cells(10 + i, 2) 'The data begins in B11
If Left(ActiveCell, 2) = "BU" Then Interior.Color = vbYellow
Next i



End Sub

 

[smallxyz]

See if this works.

Sub Shading()
    Dim PPP As Integer
    Dim NoRows As Integer
    Dim i As Integer

    Sheets("Filter").Range("B11").Select
    Selection.End(xlDown).Select
    PPP = ActiveCell.Row    'To get the last row
    NoRows = PPP - 11    'To figure out how many rows there are in my data

    For i = 1 To NoRows
        If Left(Cells(10 + i, 2), 2) = "BU" Then Cells(10 + i, 2).Interior.Color = vbYellow
    Next i
    
End Sub

 

[lrubin28]

smallxyz - thank you very much! Yes it worked fine. I'm curious though - it looks like you combined two lines into one - any idea why my original didn't work?

Either way, thanks a lot!

Larry

 

[smallxyz]

For i = 1 To NoRows
　　Set curCell = Worksheets("Filter").Cells(10 + i, 2)　'　This statement only identifies what curCell is. It has no effect on Active Cell.
　　If Left(ActiveCell, 2) = "BU" Then Interior.Color = vbYellow
Next i