Conditional formatting if number is found in a column

[Luin29]

Hi Everyone,

I would like some help writing a code that will fill in the first cell of the column if that column contains a number.

Location	Temp	pH	Conductivity
Sample A	NA	7.60	<250
Sample B	NA	<7.00	<250
Sample C	23.20	NA	<250

<tbody>
</tbody>

In the example above, the first cell in the second and third column (Temp and pH) would be filled. Below is my first attempt at writing this code:

Sub NumberinCol ()
Dim lrow As Long
Dim lcol As Long
Dim col As Range
Dim oRange As Range

lrow = Cells(Rows.Count, 1).End(xlUp).Row
lcol = Cells(1, Columns.Count).End(xlToLeft).Column
i = 2
Set oRange = Range(Range("B4"), Cells(lrow, lcol))
    For Each col InoRange.Columns
    IfoRange.Columns(i).SpecialCells(xlCellTypeConstants, 1).Count > 1 Then
        Cells(1,i).ColorIndex = 15
    End If
    i = i + 1
    Next col
End Sub

Any and all help will be greatly appreciated. Thank you!

 

[Peter_SSs]

There are a few things I don't understand about your code, including why you are starting oRange at row 4?

Anyway, see if this helps put you on the right track. If not, more details please.
Test in a copy of your workbook.

Sub NumberinCol()
  Dim lrow As Long
  Dim lcol As Long
  Dim col As Range
  Dim oRange As Range
  Dim colNums As Range
  
  lrow = Cells(Rows.Count, 1).End(xlUp).Row
  lcol = Cells(1, Columns.Count).End(xlToLeft).Column
  Set oRange = Range(Range("B1"), Cells(lrow, lcol))
  For Each col In oRange.Columns
    Set colNums = Nothing
    On Error Resume Next
    Set colNums = col.SpecialCells(xlCellTypeConstants, 1)
    On Error GoTo 0
    If Not colNums Is Nothing Then
      col.Cells(1).Interior.ColorIndex = 15
    End If
  Next col
End Sub

 

[Luin29]

Thank you Peter_SSs, your code works exactly the way I need it to.

Looking over your code, I see that it is easier to set colNums to the range of special cells and check the column for nothing with If Not rather than to see if the count of numbers in a column exceeds 1 using special cells. I originally had oRange start at row 4 because it was the first row of data that I wanted the special cells to check (rows 1-3 contained the sensor, sensor serial number, and units which I had left off in my example) but with your code I can see it can be included without causing a miscount.

 

[Peter_SSs]

... rather than to see if the count of numbers in a column exceeds 1 using special cells.

There were a few issues with your code but in relation to SpecialCells, your code would error if there are no numbers in the range being checked. That's why I used the structure I did for that part. (I also wasn't sure why you were checking for >1 if you wanted to know if there were any numbers in the column. Wouldn't it have been >0 instead?)

Thank you Peter_SSs, your code works exactly the way I need it to.

Anyway, sounds like it all worked out in the end.