G
Guest
Hi everyone,
I've been working on this piece of code and I can't find what I'm doing
wrong... please if anybody can take a look.
-Is not going through B16 to last row (when I'm doing it manualy it works)
-Somehow stops on the first 4 rows (is doing together "P" and "Ca"?)
-After this it runs to the end
Sub Check_High_Values()
Dim te As Long 'total elements
Dim LastEl As Long 'Last Element Row
Dim EndRow As Long 'Ending Row number
Dim lastC As String 'Last column letter
Dim LasR As Long ' L
Dim i As Long
Dim oRng As Range
Dim myValue As String
myfilename = Range("H3").Value
te = Range("F6").Value
LastEl = (te + 15)
EndRow = Range("B6").Value
lastC = Range("I100").Value
'look for elements and set high limits based on value
With Worksheets("ppb " & myfilename & " data").Range("B16").Select
For i = 1 To LastEl
'myValue = valueFind(ActiveCell.Offset(0, 0).Value) ' call function
to find value and check columns
On Error Resume Next
Set oRng = Cells.Find(What:=ActiveCell.Offset(0, 0).Value, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlDown)
'MatchCase:=False)
If Not oRng Is Nothing Then
If ActiveCell.Offset(0, 0).Value = "P" And _
ActiveCell.Offset(0, 3).Interior.ColorIndex = 6 Then
For Each c In Range(ActiveCell.Offset(0, 3).Address, lastC &
LastEl)
If c.Interior.ColorIndex = 6 And _
c.Value > 5000 Then
c.Interior.ColorIndex = 44
End If
Next
ElseIf ActiveCell.Offset(0, 0).Value = "Na" Or _
ActiveCell.Offset(0, 0).Value = "Mg" Or _
ActiveCell.Offset(0, 0).Value = "K" Or _
ActiveCell.Offset(0, 0).Value = "Ca" Or _
ActiveCell.Offset(0, 0).Value = "Fe" And _
ActiveCell.Offset(0, 3).Interior.ColorIndex = 6 Then
For Each c In Range(ActiveCell.Offset(0, 3).Address, lastC &
LastEl)
If c.Interior.ColorIndex = 6 And _
c.Value > 5500 Then
c.Interior.ColorIndex = 44
End If
Next
ElseIf ActiveCell.Offset(0, 0).Value <> "P" And _
ActiveCell.Offset(0, 0).Value <> "Na" And _
ActiveCell.Offset(0, 0).Value <> "Mg" And _
ActiveCell.Offset(0, 0).Value <> "K" And _
ActiveCell.Offset(0, 0).Value <> "Ca" And _
ActiveCell.Offset(0, 0).Value <> "Fe" And _
ActiveCell.Offset(0, 3).Interior.ColorIndex = 6 Then ' check
the rest
For Each c In Range(ActiveCell.Offset(0, 3).Address, lastC &
LastEl)
If c.Interior.ColorIndex = 6 And _
c.Value > 500 Then
c.Interior.ColorIndex = 44
End If
Next
End If
End If
Next i
End With
Range("H2").Select
End Sub
I've been working on this piece of code and I can't find what I'm doing
wrong... please if anybody can take a look.
-Is not going through B16 to last row (when I'm doing it manualy it works)
-Somehow stops on the first 4 rows (is doing together "P" and "Ca"?)
-After this it runs to the end
Sub Check_High_Values()
Dim te As Long 'total elements
Dim LastEl As Long 'Last Element Row
Dim EndRow As Long 'Ending Row number
Dim lastC As String 'Last column letter
Dim LasR As Long ' L
Dim i As Long
Dim oRng As Range
Dim myValue As String
myfilename = Range("H3").Value
te = Range("F6").Value
LastEl = (te + 15)
EndRow = Range("B6").Value
lastC = Range("I100").Value
'look for elements and set high limits based on value
With Worksheets("ppb " & myfilename & " data").Range("B16").Select
For i = 1 To LastEl
'myValue = valueFind(ActiveCell.Offset(0, 0).Value) ' call function
to find value and check columns
On Error Resume Next
Set oRng = Cells.Find(What:=ActiveCell.Offset(0, 0).Value, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlDown)
'MatchCase:=False)
If Not oRng Is Nothing Then
If ActiveCell.Offset(0, 0).Value = "P" And _
ActiveCell.Offset(0, 3).Interior.ColorIndex = 6 Then
For Each c In Range(ActiveCell.Offset(0, 3).Address, lastC &
LastEl)
If c.Interior.ColorIndex = 6 And _
c.Value > 5000 Then
c.Interior.ColorIndex = 44
End If
Next
ElseIf ActiveCell.Offset(0, 0).Value = "Na" Or _
ActiveCell.Offset(0, 0).Value = "Mg" Or _
ActiveCell.Offset(0, 0).Value = "K" Or _
ActiveCell.Offset(0, 0).Value = "Ca" Or _
ActiveCell.Offset(0, 0).Value = "Fe" And _
ActiveCell.Offset(0, 3).Interior.ColorIndex = 6 Then
For Each c In Range(ActiveCell.Offset(0, 3).Address, lastC &
LastEl)
If c.Interior.ColorIndex = 6 And _
c.Value > 5500 Then
c.Interior.ColorIndex = 44
End If
Next
ElseIf ActiveCell.Offset(0, 0).Value <> "P" And _
ActiveCell.Offset(0, 0).Value <> "Na" And _
ActiveCell.Offset(0, 0).Value <> "Mg" And _
ActiveCell.Offset(0, 0).Value <> "K" And _
ActiveCell.Offset(0, 0).Value <> "Ca" And _
ActiveCell.Offset(0, 0).Value <> "Fe" And _
ActiveCell.Offset(0, 3).Interior.ColorIndex = 6 Then ' check
the rest
For Each c In Range(ActiveCell.Offset(0, 3).Address, lastC &
LastEl)
If c.Interior.ColorIndex = 6 And _
c.Value > 500 Then
c.Interior.ColorIndex = 44
End If
Next
End If
End If
Next i
End With
Range("H2").Select
End Sub