Evaluate each cell in a row against two other rows using VBA

[lhara2010]

I have a lot of data (~350 rows) that need to be evaluated against two rows. Imagine the first row is a header, second row and third row are my spec limits. I am basically trying to evaluate whether each of the 350 row items fall within the values specified in Row 2 and Row 3. I will eventually need to apply this across 300 columns as well, where each column is just looking up at the first two rows. I have managed to do this successfully using conditional formatting, but for further processing it would be convenient to have a VBA program that I can manipulate.
I am not a programmer, but after quite a bit of reading and looking at other examples, I have written up something.

Sub sorting()
'
Dim i As Long
Dim iCell As Range

For i = 4 To 355
Set iCell = Range("AH" & i)
If iCell.Value > "AH2" Or iCell.Value < "AH3" Then 
iCell.Interior.Color = VBA.ColorConstants.vbRed
Cells(i, 319) = 1 'enter 1 in column LG   'added to debug code
End If
Next i

End Sub

When I run this code the entire column becomes red. I added one more line of code to check if it is just a coloring issue by entering 1 when the condition becomes true. Sure enough the whole column populates with 1. It doesn't matter what values I have in AH2 and AH3, the entire column becomes the same color. Interesting thing is, if I replace AH2 and AH3 with hard coded values like say 0 and 1, the code works correctly both for coloring and the column 319. I even tried AH$2 but it doesn't make a difference.

As I mentioned before I need this applied to ~300 columns eventually, so will likely need to add another loop or rewrite this segment of code. But I cannot figure this out.

This looked similar to another issue in this forum; but upon closer look that requirement is different

Coloring a cell based on another cell value using VBA

I usually work on it until I solve it... googling the heck out of it. But it's been days with this one and I can't just figure out what I am doing wrong. I simply need to change the color cell... if the value of an adjacent cell falls between certain range. (I CANT USE Conditional Formatting...

www.mrexcel.com

Really appreciate any help here.

Thank you all so much,

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[lhara2010]

ok, I have created an artificial dataset and rewritten the program to show the issue. It is consistent and still shows the same behavior. Sample xlsm file uploaded here

Data_sample.xlsm

Shared with Dropbox

www.dropbox.com

Sub Macro3()
'
' Macro3 Macro
'
Dim iCell As Range
For i = 4 To 13
Set iCell = Range("B" & i)
If iCell.Value > 1 Or iCell.Value < 0 Then 'how to not hardcode this
iCell.Interior.Color = VBA.ColorConstants.vbRed
Cells(i, 13) = 1 'enter 1 in column M
End If
Next i

'
End Sub

For the line where I commented "how to not hardcode this", I tried replacing 1 and 0 with B2 and B3.

That line would look like "If iCell.Value > "B2" Or iCell.Value < "B3" Then

If I do this, the entire column becomes red. The verification column M shows that the condition is being evaluated true for all rows. Why is this happening and how to circumvent this?

As a next step, I would like to apply this for all the other columns, would that require looping through all the columns?

 

[mumps]

Try:

Sub CompareData()
    Application.ScreenUpdating = False
    Dim v As Variant, r As Long, c As Long, lCol As Long, x As Double, y As Double, z As Double
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column
    v = Range("B2", Range("B" & Rows.Count).End(xlUp)).Resize(, lCol - 1).Value
    For r = 3 To UBound(v)
        For c = LBound(v, 2) To UBound(v, 2)
            If v(r, c) > v(1, c) Or v(r, c) < v(2, c) Then
                Cells(r + 1, c + 1).Interior.ColorIndex = 3
            End If
        Next c
    Next r
    Application.ScreenUpdating = True
End Sub

 

[lhara2010]

Thank you for this, works like a charm. Are you able to tell me what I did wrong with my original code?

 

[mumps]

You are very welcome. Your code would only work on column B whereas my code loops through all the rows and columns. Also, my code uses arrays so it is very fast.