Loop to change cell color based on found value?
Well, I got this put together and as usual I'm doing something silly.
Can anybody see what's wrong? Is the way I'm calling the function? or the
function itself? I'm sure there is a better way to write the code, but I have
to go step by step and see what I'm doing (or trying to do)
Thanks so much...
Sub Check_High_Values()
Dim te As Long 'total elements
Dim LastEl As Long 'Last Element Row
Dim LasR As Long ' Last Row number
Dim lastC As String 'Last column letter
Dim i As Long
Dim myValue As String
myfilename = Range("H3").Value
te = Range("F6").Value
LastEl = (te + 15)
lastC = Range("I100").Value
'look for elements and set high limits
Sheets("ppb " & myfilename & " data").Range("A16").Select
For i = 1 To LastEl
myValue = valueFind(myValue) ' call function to find value and check
columns
Next i
End Sub
Function valueFind(val As String)
Dim oRng As Range
myfilename = Range("H3").Value
With Worksheets("ppb " & myfilename & " data")
On Error Resume Next
Set oRng = Cells.Find(What:="value", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
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
End If
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
End If
ElseIf 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
End With
Range("H2").Select
End Function
"gaba" wrote:
Hello there,
I'm trying to find a value in column "B16","B46" and based on that value set
the upper limits to the cells in that row starting at column E to the end
(last column)
I need to match certain conditions after finding the desired value. The
Colored cells begin on column E, the same for the values to be checked and
colored orange
I've been trying to use ActiveCell.Offset but I can only go through rows or
through columns, not both.
i.e.
If "P" And Cell.Interior.ColorIndex = 6 And Cell.value 5000
ActiveCell.Interior.ColorIndex = 44
If "Na" or "Mg" or "K" And Cell.Interior.ColorIndex = 6 And Cell.value 5500
ActiveCell.Interior.ColorIndex = 44
If (Rest of cells) And Cell.Interior.ColorIndex = 6 And Cell.value 500
ActiveCell.Interior.ColorIndex = 44
Any help will be more than appreciated. Right now I'm "looping" and
confusing myself with the best approach
--
Gaba
|