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

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

Functions return values. I didn't see where your function was returning
anything.

They don't have to return anything meaningful, but if I want to use a function
like a subroutine, I'll usually return a boolean (true/false) describing how the
function worked (good or bad).

But I'm not sure what:
myValue = valueFind(myValue)
is supposed to do.

Usually, you'll see something like this:

Option Explicit
Sub testme()
Dim m As Variant
m = myFunc(3)
MsgBox m
End Sub
Function myFunc(a As Long) As Variant
myFunc = a * 3
End Function

And you've got a couple of things that make me nervous--but I'm not sure I
understood what was going on.

I didn't see where some of your variables were getting set. And you can't use
variables in one routine (your Sub) in another routine (your function) without
passing them--or making those variables "visible" to the other function. By
declaring some of your variables outside the Subs and Functions, they'll be
visible to any routine in that module.

And in your .find statement (in the function), you're using With/end with, but
you didn't qualify the range (cells) correctly. You'll want to use a leading
dot (.cells.find) so that excel knows that you're talking about the previous
With statement's object.

And you actually looked for "value". But you passed val. I changed that.

I also changed all your activecell's to oRng. I figured that you meant the cell
that was found--not where the cursor happened to be sitting at the moment.

I also changed your if/endif/elseif's to what I thought made sense.

But all this was pure conjecture. But it may give you some ideas on how to
approach your problem.



Option Explicit
Dim te As Long
Dim LastEl As Long
Dim lastR As Long
Dim lastC As String
Dim mySheetName As String

Sub Check_High_Values()

Dim LasR As Long ' Last Row number

Dim i As Long
Dim myValue As String
Dim myFileName As String

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

mySheetName = "ppb " & myFileName & " data"
For i = 1 To LastEl
'call function to find value and check Columns
myValue = valueFind(myValue)
Next i
End Sub

Function valueFind(val As String)
Dim oRng As Range
Dim c As Range

With Worksheets(mySheetName)
Set oRng = .Cells.Find(What:=val, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not oRng Is Nothing Then
If oRng.Offset(0, 0).Value = "P" And _
oRng.Offset(0, 3).Interior.ColorIndex = 6 Then
For Each c In .Range(oRng.Offset(0, 3), lastC & LastEl)
If c.Interior.ColorIndex = 6 And _
c.Value 5000 Then
c.Interior.ColorIndex = 44
End If
Next c
ElseIf oRng.Offset(0, 0).Value = "Na" Or _
oRng.Offset(0, 0).Value = "Mg" Or _
oRng.Offset(0, 0).Value = "K" Or _
oRng.Offset(0, 0).Value = "Ca" Or _
oRng.Offset(0, 0).Value = "Fe" And _
oRng.Offset(0, 3).Interior.ColorIndex = 6 Then
For Each c In .Range(oRng.Offset(0, 3), lastC & LastEl)
If c.Interior.ColorIndex = 6 And _
c.Value 5500 Then
c.Interior.ColorIndex = 44
End If
Next c
ElseIf oRng.Offset(0, 3).Interior.ColorIndex = 6 Then 'check the rest
For Each c In .Range(oRng.Offset(0, 3).Address, lastC & LastEl)
If c.Interior.ColorIndex = 6 And _
c.Value 500 Then
c.Interior.ColorIndex = 44
End If
Next c
End If
End If
End With

End Function


gaba wrote:

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

--

Dave Peterson

Dave, thanks so much for your anwer/help. Your explanation made clear what I
was trying to do (call a function to get some value) was the wrong approach.

I've made the changes you suggested (oRng instead of ActiveCell, etc.) and
nested the loops (the loop looking for the Yellow colored cells with high
values through the columns inside the one looking for the string in column
B).

Now is doing what I intended.... Thanks so much.

I've printed a lot of material on functions and how to call them... Time to
catch up.

Gaba

"Dave Peterson" wrote:

Functions return values. I didn't see where your function was returning
anything.

They don't have to return anything meaningful, but if I want to use a function
like a subroutine, I'll usually return a boolean (true/false) describing how the
function worked (good or bad).

But I'm not sure what:
myValue = valueFind(myValue)
is supposed to do.

Usually, you'll see something like this:

Option Explicit
Sub testme()
Dim m As Variant
m = myFunc(3)
MsgBox m
End Sub
Function myFunc(a As Long) As Variant
myFunc = a * 3
End Function

And you've got a couple of things that make me nervous--but I'm not sure I
understood what was going on.

I didn't see where some of your variables were getting set. And you can't use
variables in one routine (your Sub) in another routine (your function) without
passing them--or making those variables "visible" to the other function. By
declaring some of your variables outside the Subs and Functions, they'll be
visible to any routine in that module.

And in your .find statement (in the function), you're using With/end with, but
you didn't qualify the range (cells) correctly. You'll want to use a leading
dot (.cells.find) so that excel knows that you're talking about the previous
With statement's object.

And you actually looked for "value". But you passed val. I changed that.

I also changed all your activecell's to oRng. I figured that you meant the cell
that was found--not where the cursor happened to be sitting at the moment.

I also changed your if/endif/elseif's to what I thought made sense.

But all this was pure conjecture. But it may give you some ideas on how to
approach your problem.



Option Explicit
Dim te As Long
Dim LastEl As Long
Dim lastR As Long
Dim lastC As String
Dim mySheetName As String

Sub Check_High_Values()

Dim LasR As Long ' Last Row number

Dim i As Long
Dim myValue As String
Dim myFileName As String

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

mySheetName = "ppb " & myFileName & " data"
For i = 1 To LastEl
'call function to find value and check Columns
myValue = valueFind(myValue)
Next i
End Sub

Function valueFind(val As String)
Dim oRng As Range
Dim c As Range

With Worksheets(mySheetName)
Set oRng = .Cells.Find(What:=val, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not oRng Is Nothing Then
If oRng.Offset(0, 0).Value = "P" And _
oRng.Offset(0, 3).Interior.ColorIndex = 6 Then
For Each c In .Range(oRng.Offset(0, 3), lastC & LastEl)
If c.Interior.ColorIndex = 6 And _
c.Value 5000 Then
c.Interior.ColorIndex = 44
End If
Next c
ElseIf oRng.Offset(0, 0).Value = "Na" Or _
oRng.Offset(0, 0).Value = "Mg" Or _
oRng.Offset(0, 0).Value = "K" Or _
oRng.Offset(0, 0).Value = "Ca" Or _
oRng.Offset(0, 0).Value = "Fe" And _
oRng.Offset(0, 3).Interior.ColorIndex = 6 Then
For Each c In .Range(oRng.Offset(0, 3), lastC & LastEl)
If c.Interior.ColorIndex = 6 And _
c.Value 5500 Then
c.Interior.ColorIndex = 44
End If
Next c
ElseIf oRng.Offset(0, 3).Interior.ColorIndex = 6 Then 'check the rest
For Each c In .Range(oRng.Offset(0, 3).Address, lastC & LastEl)
If c.Interior.ColorIndex = 6 And _
c.Value 500 Then
c.Interior.ColorIndex = 44
End If
Next c
End If
End If
End With

End Function


gaba wrote:

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

--

Dave Peterson