Highlighting Rows when different cell criteria falls into a specified range

[CMexcel]

I am new to VBA (as in I started today) but have some experience with Matlab. I used excel formulas to create a box that when a length, height, and depth are imputed, ranges are created for each by adding plus and minus .25, or whatever you want. I have a table below it also with a column for lengths, widths and heights. I am trying to make it so the values in these rows will height up when all 3 (length, width, and height) fall into the ranges created described earlier. I really have been struggling but I will post my butchered code anyways. Also, how do I enter down a line in the If statement without an error? Using an underscore didn't seem to work for some reason.

Private Sub CommandButton2_Click()

Dim i As Long
For i = 10 To 50
If Range("a.i").Value >= Range("H4").Value And Range("a.i").Value <= Range("H4").Value And Range("b.i").Value >= Range("J4").Value And Range("b.i").Value <= Range("K4").Value And Range("c.i").Value >= Range("L4").Value And Range("c.i").Value <= Range("M4").Value Then cell.EntireRow.Interior.ColorIndex = 7
Next i

End Sub
[code]

Thank you greatly

 

[Mike LH]

Hi,


Welcome to the board.


Your code is below with all the syntax corrected but I thin you've gotten yourself a bit mixed up. See the bold line in the code. It's over complicated because you could simply say

If Range("A" & i).Value = Range("H4").Value

However this may get you going in the right direction.

Private Sub CommandButton2_Click()
 Dim i As Long
 For i = 10 To 50
[B] If Range("A" & i).Value >= Range("H4").Value And Range("A" & i).Value <= Range("H4").Value [/B]And _
 Range("B" & i).Value >= Range("J4").Value And Range("B" & i).Value <= Range("K4").Value And _
 Range("C" & i).Value >= Range("L4").Value And Range("C" & i).Value <= Range("M4").Value Then
 Rows(i).EntireRow.Interior.ColorIndex = 7
 End If
 Next i
 End Sub

 

[CMexcel]

Thanks! I got it working now!

 

[CMexcel]

Could someone help me on the proper syntax for the following line of code? I am trying to instead of using the entirerow highlight, stop it at column t.


For i=10 To 50
...
Range("A:T" & i).Interior.ColorIndex = 6
...


I know that the "A:T" is incorrect, I just don't know how to properly write it.

Also, how do I do code boxes on this forum?

Thanks!

 

[CMexcel]

Bump