How to loop through and select a row using VBA and variables

[philwojo]

OK, I know this is super easy but I am struggling with it.

I want to do a test to check a cell for a specific value, in this case 3000, I know how to do that and have that part working.

Now once I find that cell with the 3000 value I want to highlight that row from column-B through Column-BF.

This is the part I am struggling with what VBA code do I use to do this, here is what I have now, but it isn't working:

Range(Cells(rng, lastrow)).Interior.TintAndShade = -0.249977111117893

I know that isn't right, I am just not sure how to change it. "rng" is set to .Range("B7:B6000"), I also have this:
Dim textlen As Integer, rng As Range, Cell As Range, RecordLen As Integer

Thanks for any feedback, I want to learn how to do this but I am just stuck, maybe because it is Friday.

 

[Rick Rothstein]

I want to do a test to check a cell for a specific value, in this case 3000, I know how to do that and have that part working.

Show us the code you have so that we know how to tell you to modify it for what you want to so with it.

 

[philwojo]

Private Sub Highlight3k()

Dim textlen As Integer, rng As Range, Cell As Range, RecordLen As Integer


With ThisWorkbook.Worksheets("PIF Checker Output - Horz")


    Set rng = .Range("B7:B6000")
    RecordLen = 3000
    
    For Each Cell In rng
      
    If Cell.Value = RecordLen Then
        Range(Cells(rng, lastrow)).Interior.TintAndShade = -0.249977111117893


    End If
    Next Cell
    
End With




End Sub

 

[Fluff]

Try

    If Cell.Value = RecordLen Then
        Cell.Resize(, 57).Interior.TintAndShade = -0.249977111117893
    End If

 

[philwojo]

Thanks Fluff, that worked.

Is there a way to make my code more efficient so it doesn't loop all the way to B6000 if there is no data?

 

[Rick Rothstein]

Private Sub Highlight3k()

Dim textlen As Integer, rng As Range, Cell As Range, RecordLen As Integer

With ThisWorkbook.Worksheets("PIF Checker Output - Horz")

    Set rng = .Range("B7:B6000")
    RecordLen = 3000
    
    For Each Cell In rng
      
    If Cell.Value = RecordLen Then
        Range(Cells(rng, lastrow)).Interior.TintAndShade = -0.249977111117893

    End If
    Next Cell
    
End With

End Sub

First off, before you can TintAndShade a cell, you need to give it a color which I don't see you doing in the code above. Did you omit doing this or, if you already have something colored, what is it... the cell in Column B or all the cells in Columns B:BF?

 

[Fluff]

Like

Private Sub Highlight3k()

Dim Cell As Range, RecordLen As Integer


With ThisWorkbook.Worksheets("PIF Checker Output - Horz")

    RecordLen = 3000
    
    For Each Cell In .Range("B7", .Range("B" & Rows.Count).End(xlUp))
      If Cell.Value = RecordLen Then
          Cell.Resize(, 57).Interior.TintAndShade = -0.249977111117893
      End If
    Next Cell
End With

End Sub

 

[philwojo]

First off, before you can TintAndShade a cell, you need to give it a color which I don't see you doing in the code above. Did you omit doing this or, if you already have something colored, what is it... the cell in Column B or all the cells in Columns B:BF?

I am doing this through another bit of code, but thanks for that tip, I was not aware of that.

Fluff, thanks for the new code it is working great. Now I have to look it over so I can educate myself and learn from this, I'm getting better, but still not where I want to be with it.

 

[Rick Rothstein]

Fluff, thanks for the new code it is working great. Now I have to look it over so I can educate myself and learn from this, I'm getting better, but still not where I want to be with it.

Here is Fluff's code line...

Cell.Resize(, 57).Interior.TintAndShade = -0.249977111117893

What I highlighted in red can be replaced with this....

Intersect(Cell.EntireRow, Columns("B:BF"))

Not sure if that helps you see what is going on or not, but I thought I would post it just in case.

 

[philwojo]

Thanks Rick, I will read up on the differences between the two so I can better understand. I know that Column-BF is the same as 57 in Fluff's code though.