Help with a For Loop that compares and formats cells in a range

[seattletimebandit]

I'm looking to write a macro that loops through a table to compare data and make format changes.

I have a table that I need to compare data in cells in a Range (by selecting the range) to data in other cells (all in a single Column down to x rows). If the data in the Selected Range is ">=" the data in the adjacent Column C, then the cells in the Range that meet certain criteria are highlighted.

The criteria are that if the cell is empty or if there is some text, ignore it, only numeric values need to be highlighted.

I've tried to "talk" it out by the following:

1. Check to see if there are any numbers in the cells in column B (starting in B3 down to B100), if the cell is empty, continue down each row in column B until a number is found.
2. if a numeric value is found in the cell in Column B, then starting checking cells in the Selected Range, moving to the right (Column 3 through Column 103) in that same row, searching for numeric values to the end of row (ending at Column 103)
3. if a cell is blank or has text (non-numeric) keep moving along the row to the end
4. if cell has a numeric value, compare it to the value found Column B (in that same row) do the following
   1. if value is "<" the column B value, ignore it
   2. if cell value is ">=" to the Column B value, change cell color (I have a bit of formatting code for that)
5. After checking and changing the cells with values in the row, move to next row starting back in Column C
6. repeat above until no data found (col 100, row 100)

The following snippet works when selecting a row and comparing the cells in the selection to a specific cell ($B3). Some the data in cells are numbers:
1.25, 5.13, etc. and some have a letter 0.500 U, 1.00 U. I'm ignoring the cells with "U's".

I just need to loop through a row, meet the criteria, then loop through columns, meet the criteria, then start again on next row to the end.


Thanks in advance!

Sub ChangeCellColor()
Dim c As Range




For Each c In Selection
    c.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=$B3"
        If Right(c.Value, 1) <> "U" Then
            With c.FormatConditions(1)
                .Interior.PatternColorIndex = xlAutomatic
                .Interior.Color = RGB(197, 217, 241)
                .Font.ColorIndex = 1
            End With
        End If
Next c


End Sub

 

[seattletimebandit]

Here is a small portion of the table I'm working with to help with visualizing. The laboratory data with "U's" are non-detect for the compounds analyzed. The
bold numbers are results above the lab's detection limits, the Cleanup Levels in column B are the cells I need to find in the loop then
move across the row to check each cell, compare to the Cleanup Level, and highlight any cells that are greater than or equal to the
Cleanup Level (I couldn't fill the cells in this post, but most of the cells in Row 16 would be filled). Note there are some empty cells that need to be skipped in both Column B, and some in the rows (in this case Cols F and G).

If you paste this table into Excel, select any row and run the macro I posted, cells with data above "100" will be fill with a light blue color.

		MW-01D	MW-01D	MW-02D	MW-02D	MW-03D	MW-03D	MW-04D	MW-04D
CONSTITUENT	CleanupLevel	4/11/2011	10/18/2011	4/11/2011	10/19/2011	4/13/2011	10/17/2011	4/14/2011	10/17/2011
Dichlorodifluoromethane	100	1 U	1.00 U	1 U	1.00 U	1 U	1.00 U	1 U	1.00 U
Chloromethane		0.5 U	0.500 U	0.5 U	0.500 U	0.5 U	0.500 U	0.5 U	0.500 U
Vinyl chloride	100	0.2 U	0.2 U	3.23	1.12	4.62	3.59	0.67	1.1
Bromomethane		0.5 U	0.5 U		0.500 U		0.500 U	0.500 U	0.500 U
Trichlorofluoromethane		0.5 U	0.5 U		0.500 U		0.500 U	0.500 U	0.500 U
Chloroethane		0.91	0.5 U	0.5 U	0.500 U	0.52	0.500 U	0.500 U	0.500 U
1,1-Dichloroethene	1000	17.2	0.98	12.3	4.24	16.8	12.3	0.86	3.46
Acetone		2 U	2 U	2 U	2.00 U	2.00 U	2.00 U	2.00 U	2.00 U
Methylene chloride	25	3.68	0.86	2.84	0.64	2.49	1.84	0.500 U	0.6
trans-1,2-Dichloroethene		184	64.9	124	61.3	125	111	61.7	60.2
1,1-Dichloroethane		275	7.08	164	55.3	263	174	5.87	50.8
2,2-Dichloropropane		1 U	1 U	1 U	1.00 U	1.00 U	1.00 U	1.00 U	1.00 U
cis-1,2-Dichloroethene	1100	2700	109	2200	862	2480	1860	90.8	703
Chloroform		1 U	1 U	1 U	1.00 U	1.00 U	1.00 U	1.00 U	1.00 U

<tbody>
</tbody>

 

[mumps]

Try:

Sub Test()
    Application.ScreenUpdating = False
    Dim lastColumn As Integer
    Dim c1 As Range
    Dim c2 As Range
    For Each c1 In Range("B3:B100")
        If c1 <> "" And IsNumeric(c1) Then
        lastColumn = ActiveSheet.Cells(c1.Row, Columns.Count).End(xlToLeft).Column
            For Each c2 In Range(Cells(c1.Row, 3), Cells(c1.Row, lastColumn))
                If c2 <> "" And IsNumeric(c2) And c2 >= c1 Then
                    c2.Interior.ColorIndex = 3
                End If
            Next c2
        End If
    Next c1
    Application.ScreenUpdating = True
End Sub

 

[seattletimebandit]

Mumps! You rock!

Most excellent Sir (or Madam)!

First run through it seemed to work as requested. Huge help! I knew I was close, but the nested For Loops were throwing me for a loop...(pun intended).

Much appreciated. That little snippet will come in hand in so many similar applications.

 

[mumps]

Glad I could help.