Find and loop
 

I have a fairly simple find and loop set up to find and color certian rows.
Currently I see two problems: First, my code finds my find value, but does
not appear to meet my IF THEN criteria. Meaning it has added color to a
row that does not meet this criteria in my code... If rngFound.Offset(0,
-11).Value Sheets("data").Range("z4").Value Then "Color the row"... Is my
offset the wrong number? I have my find column in column U and the value I
am picking in the offset is in column J... isn't that offset(0,-11)?

The other thing is that when it finds and colors the first row in the loop,
it then ends the code, instead of continuing to find the rest of the rows
that meet the criteria.

Here is the code Thanks for the help!



Dim rngToSearch As Range
Dim wks As Worksheet
Dim rngFound As Range
Sheets("corps").Select
Call FindErrors
Range("u10").Select
Selection.EntireColumn.Hidden = False
Set wks = Sheets("corps")
Set rngToSearch = wks.Range("u11:u712")
Set rngFound = rngToSearch.find(what:="1", LookIn:=xlValues,
lookat:=xlWhole)

If rngFound.Offset(0, -11).Value Sheets("data").Range("z4").Value Then
saddr = rngFound.Address
r = rngFound.Row
Do
Range("a" & r, "s" & r).Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With

Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound Is Nothing Or rngFound.Address = saddr
Range("u10").Select
Selection.EntireColumn.Hidden = True
end if
end sub

Find and loop
 

John,

Try the code below.

HTH,
Bernie
MS Excel MVP


Sub FindValues()
Dim myFindString As String
Dim FirstAddress As String
Dim wks As Worksheet
Dim rngFound As Range
Dim rngToSearch As Range

myFindString = "1"
Set wks = Worksheets("corps")
wks.Select

'Call FindErrors

wks.Range("u10").EntireColumn.Hidden = False
Set rngToSearch = wks.Range("u11:u712")

With rngToSearch

Set rngFound = .Find(myFindString, _
LookIn:=xlValues, lookAt:=xlWhole)

If Not rngFound Is Nothing Then
FirstAddress = rngFound.Address
If wks.Cells(rngFound.Row, "J").Value _
Sheets("data").Range("z4").Value Then
With wks.Range("A" & rngFound.Row).Resize(1, 19).Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
End If
Else:
MsgBox "Not Found"
End
End If

Set rngFound = .FindNext(rngFound)
If Not rngFound Is Nothing And rngFound.Address < _
FirstAddress Then
Do
If wks.Cells(rngFound.Row, "J").Value _
Sheets("data").Range("z4").Value Then
With wks.Range("A" & rngFound.Row).Resize(1, 19).Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
End If

Set rngFound = .FindNext(rngFound)
Loop While Not rngFound Is Nothing And _
rngFound.Address < FirstAddress
End If
End With
Range("u10").EntireColumn.Hidden = True

End Sub




"John" wrote in message
...
I have a fairly simple find and loop set up to find and color certian rows.
Currently I see two problems: First, my code finds my find value, but does
not appear to meet my IF THEN criteria. Meaning it has added color to a
row that does not meet this criteria in my code... If rngFound.Offset(0,
-11).Value Sheets("data").Range("z4").Value Then "Color the row"... Is my
offset the wrong number? I have my find column in column U and the value I
am picking in the offset is in column J... isn't that offset(0,-11)?

The other thing is that when it finds and colors the first row in the loop,
it then ends the code, instead of continuing to find the rest of the rows
that meet the criteria.

Here is the code Thanks for the help!



Dim rngToSearch As Range
Dim wks As Worksheet
Dim rngFound As Range
Sheets("corps").Select
Call FindErrors
Range("u10").Select
Selection.EntireColumn.Hidden = False
Set wks = Sheets("corps")
Set rngToSearch = wks.Range("u11:u712")
Set rngFound = rngToSearch.find(what:="1", LookIn:=xlValues,
lookat:=xlWhole)

If rngFound.Offset(0, -11).Value Sheets("data").Range("z4").Value Then
saddr = rngFound.Address
r = rngFound.Row
Do
Range("a" & r, "s" & r).Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With

Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound Is Nothing Or rngFound.Address = saddr
Range("u10").Select
Selection.EntireColumn.Hidden = True
end if
end sub

Find and loop
 

Bernie, thanks for the reply. The code almost has it. It colors all the 1
values, but but still colors many lines that are less than my target value...
they are dates, does that matter? I am looking to see if it is a quick
change myself.

thanks again

"Bernie Deitrick" wrote:

John,

Try the code below.

HTH,
Bernie
MS Excel MVP


Sub FindValues()
Dim myFindString As String
Dim FirstAddress As String
Dim wks As Worksheet
Dim rngFound As Range
Dim rngToSearch As Range

myFindString = "1"
Set wks = Worksheets("corps")
wks.Select

'Call FindErrors

wks.Range("u10").EntireColumn.Hidden = False
Set rngToSearch = wks.Range("u11:u712")

With rngToSearch

Set rngFound = .Find(myFindString, _
LookIn:=xlValues, lookAt:=xlWhole)

If Not rngFound Is Nothing Then
FirstAddress = rngFound.Address
If wks.Cells(rngFound.Row, "J").Value _
Sheets("data").Range("z4").Value Then
With wks.Range("A" & rngFound.Row).Resize(1, 19).Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
End If
Else:
MsgBox "Not Found"
End
End If

Set rngFound = .FindNext(rngFound)
If Not rngFound Is Nothing And rngFound.Address < _
FirstAddress Then
Do
If wks.Cells(rngFound.Row, "J").Value _
Sheets("data").Range("z4").Value Then
With wks.Range("A" & rngFound.Row).Resize(1, 19).Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
End If

Set rngFound = .FindNext(rngFound)
Loop While Not rngFound Is Nothing And _
rngFound.Address < FirstAddress
End If
End With
Range("u10").EntireColumn.Hidden = True

End Sub




"John" wrote in message
...
I have a fairly simple find and loop set up to find and color certian rows.
Currently I see two problems: First, my code finds my find value, but does
not appear to meet my IF THEN criteria. Meaning it has added color to a
row that does not meet this criteria in my code... If rngFound.Offset(0,
-11).Value Sheets("data").Range("z4").Value Then "Color the row"... Is my
offset the wrong number? I have my find column in column U and the value I
am picking in the offset is in column J... isn't that offset(0,-11)?

The other thing is that when it finds and colors the first row in the loop,
it then ends the code, instead of continuing to find the rest of the rows
that meet the criteria.

Here is the code Thanks for the help!



Dim rngToSearch As Range
Dim wks As Worksheet
Dim rngFound As Range
Sheets("corps").Select
Call FindErrors
Range("u10").Select
Selection.EntireColumn.Hidden = False
Set wks = Sheets("corps")
Set rngToSearch = wks.Range("u11:u712")
Set rngFound = rngToSearch.find(what:="1", LookIn:=xlValues,
lookat:=xlWhole)

If rngFound.Offset(0, -11).Value Sheets("data").Range("z4").Value Then
saddr = rngFound.Address
r = rngFound.Row
Do
Range("a" & r, "s" & r).Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With

Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound Is Nothing Or rngFound.Address = saddr
Range("u10").Select
Selection.EntireColumn.Hidden = True
end if
end sub

Find and loop
 

John,

It worked fine for me with dates. Are you sure you have actual dates, and not strings that look
like dates? Try formatting the cells as numbers, and the date values should show numbers like
38600.00 not "9/5/2005" 38600 is the date value of September 5.

HTH,
Bernie
MS Excel MVP


"John" wrote in message
...
Bernie, thanks for the reply. The code almost has it. It colors all the 1
values, but but still colors many lines that are less than my target value...
they are dates, does that matter? I am looking to see if it is a quick
change myself.

thanks again

"Bernie Deitrick" wrote:

John,

Try the code below.

HTH,
Bernie
MS Excel MVP


Sub FindValues()
Dim myFindString As String
Dim FirstAddress As String
Dim wks As Worksheet
Dim rngFound As Range
Dim rngToSearch As Range

myFindString = "1"
Set wks = Worksheets("corps")
wks.Select

'Call FindErrors

wks.Range("u10").EntireColumn.Hidden = False
Set rngToSearch = wks.Range("u11:u712")

With rngToSearch

Set rngFound = .Find(myFindString, _
LookIn:=xlValues, lookAt:=xlWhole)

If Not rngFound Is Nothing Then
FirstAddress = rngFound.Address
If wks.Cells(rngFound.Row, "J").Value _
Sheets("data").Range("z4").Value Then
With wks.Range("A" & rngFound.Row).Resize(1, 19).Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
End If
Else:
MsgBox "Not Found"
End
End If

Set rngFound = .FindNext(rngFound)
If Not rngFound Is Nothing And rngFound.Address < _
FirstAddress Then
Do
If wks.Cells(rngFound.Row, "J").Value _
Sheets("data").Range("z4").Value Then
With wks.Range("A" & rngFound.Row).Resize(1, 19).Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
End If

Set rngFound = .FindNext(rngFound)
Loop While Not rngFound Is Nothing And _
rngFound.Address < FirstAddress
End If
End With
Range("u10").EntireColumn.Hidden = True

End Sub




"John" wrote in message
...
I have a fairly simple find and loop set up to find and color certian rows.
Currently I see two problems: First, my code finds my find value, but does
not appear to meet my IF THEN criteria. Meaning it has added color to a
row that does not meet this criteria in my code... If rngFound.Offset(0,
-11).Value Sheets("data").Range("z4").Value Then "Color the row"... Is my
offset the wrong number? I have my find column in column U and the value I
am picking in the offset is in column J... isn't that offset(0,-11)?

The other thing is that when it finds and colors the first row in the loop,
it then ends the code, instead of continuing to find the rest of the rows
that meet the criteria.

Here is the code Thanks for the help!



Dim rngToSearch As Range
Dim wks As Worksheet
Dim rngFound As Range
Sheets("corps").Select
Call FindErrors
Range("u10").Select
Selection.EntireColumn.Hidden = False
Set wks = Sheets("corps")
Set rngToSearch = wks.Range("u11:u712")
Set rngFound = rngToSearch.find(what:="1", LookIn:=xlValues,
lookat:=xlWhole)

If rngFound.Offset(0, -11).Value Sheets("data").Range("z4").Value Then
saddr = rngFound.Address
r = rngFound.Row
Do
Range("a" & r, "s" & r).Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With

Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound Is Nothing Or rngFound.Address = saddr
Range("u10").Select
Selection.EntireColumn.Hidden = True
end if
end sub

Find and loop
 

Bernie, you are right, my J column is in a text format since all the values
are linked to a page recieved via a download. Is there any way to format
column J as a number or date, not text?
If I go into the cell and backspace infront of the date this works to change
the format, but I don't think I am actually deleting a space... if that info
helps

"Bernie Deitrick" wrote:

John,

It worked fine for me with dates. Are you sure you have actual dates, and not strings that look
like dates? Try formatting the cells as numbers, and the date values should show numbers like
38600.00 not "9/5/2005" 38600 is the date value of September 5.

HTH,
Bernie
MS Excel MVP


"John" wrote in message
...
Bernie, thanks for the reply. The code almost has it. It colors all the 1
values, but but still colors many lines that are less than my target value...
they are dates, does that matter? I am looking to see if it is a quick
change myself.

thanks again

"Bernie Deitrick" wrote:

John,

Try the code below.

HTH,
Bernie
MS Excel MVP


Sub FindValues()
Dim myFindString As String
Dim FirstAddress As String
Dim wks As Worksheet
Dim rngFound As Range
Dim rngToSearch As Range

myFindString = "1"
Set wks = Worksheets("corps")
wks.Select

'Call FindErrors

wks.Range("u10").EntireColumn.Hidden = False
Set rngToSearch = wks.Range("u11:u712")

With rngToSearch

Set rngFound = .Find(myFindString, _
LookIn:=xlValues, lookAt:=xlWhole)

If Not rngFound Is Nothing Then
FirstAddress = rngFound.Address
If wks.Cells(rngFound.Row, "J").Value _
Sheets("data").Range("z4").Value Then
With wks.Range("A" & rngFound.Row).Resize(1, 19).Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
End If
Else:
MsgBox "Not Found"
End
End If

Set rngFound = .FindNext(rngFound)
If Not rngFound Is Nothing And rngFound.Address < _
FirstAddress Then
Do
If wks.Cells(rngFound.Row, "J").Value _
Sheets("data").Range("z4").Value Then
With wks.Range("A" & rngFound.Row).Resize(1, 19).Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
End If

Set rngFound = .FindNext(rngFound)
Loop While Not rngFound Is Nothing And _
rngFound.Address < FirstAddress
End If
End With
Range("u10").EntireColumn.Hidden = True

End Sub




"John" wrote in message
...
I have a fairly simple find and loop set up to find and color certian rows.
Currently I see two problems: First, my code finds my find value, but does
not appear to meet my IF THEN criteria. Meaning it has added color to a
row that does not meet this criteria in my code... If rngFound.Offset(0,
-11).Value Sheets("data").Range("z4").Value Then "Color the row"... Is my
offset the wrong number? I have my find column in column U and the value I
am picking in the offset is in column J... isn't that offset(0,-11)?

The other thing is that when it finds and colors the first row in the loop,
it then ends the code, instead of continuing to find the rest of the rows
that meet the criteria.

Here is the code Thanks for the help!



Dim rngToSearch As Range
Dim wks As Worksheet
Dim rngFound As Range
Sheets("corps").Select
Call FindErrors
Range("u10").Select
Selection.EntireColumn.Hidden = False
Set wks = Sheets("corps")
Set rngToSearch = wks.Range("u11:u712")
Set rngFound = rngToSearch.find(what:="1", LookIn:=xlValues,
lookat:=xlWhole)

If rngFound.Offset(0, -11).Value Sheets("data").Range("z4").Value Then
saddr = rngFound.Address
r = rngFound.Row
Do
Range("a" & r, "s" & r).Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With

Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound Is Nothing Or rngFound.Address = saddr
Range("u10").Select
Selection.EntireColumn.Hidden = True
end if
end sub

Find and loop
 

I will add 0 to the cell... that works

I appreciate the help


"Bernie Deitrick" wrote:

John,

It worked fine for me with dates. Are you sure you have actual dates, and not strings that look
like dates? Try formatting the cells as numbers, and the date values should show numbers like
38600.00 not "9/5/2005" 38600 is the date value of September 5.

HTH,
Bernie
MS Excel MVP


"John" wrote in message
...
Bernie, thanks for the reply. The code almost has it. It colors all the 1
values, but but still colors many lines that are less than my target value...
they are dates, does that matter? I am looking to see if it is a quick
change myself.

thanks again

"Bernie Deitrick" wrote:

John,

Try the code below.

HTH,
Bernie
MS Excel MVP


Sub FindValues()
Dim myFindString As String
Dim FirstAddress As String
Dim wks As Worksheet
Dim rngFound As Range
Dim rngToSearch As Range

myFindString = "1"
Set wks = Worksheets("corps")
wks.Select

'Call FindErrors

wks.Range("u10").EntireColumn.Hidden = False
Set rngToSearch = wks.Range("u11:u712")

With rngToSearch

Set rngFound = .Find(myFindString, _
LookIn:=xlValues, lookAt:=xlWhole)

If Not rngFound Is Nothing Then
FirstAddress = rngFound.Address
If wks.Cells(rngFound.Row, "J").Value _
Sheets("data").Range("z4").Value Then
With wks.Range("A" & rngFound.Row).Resize(1, 19).Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
End If
Else:
MsgBox "Not Found"
End
End If

Set rngFound = .FindNext(rngFound)
If Not rngFound Is Nothing And rngFound.Address < _
FirstAddress Then
Do
If wks.Cells(rngFound.Row, "J").Value _
Sheets("data").Range("z4").Value Then
With wks.Range("A" & rngFound.Row).Resize(1, 19).Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
End If

Set rngFound = .FindNext(rngFound)
Loop While Not rngFound Is Nothing And _
rngFound.Address < FirstAddress
End If
End With
Range("u10").EntireColumn.Hidden = True

End Sub




"John" wrote in message
...
I have a fairly simple find and loop set up to find and color certian rows.
Currently I see two problems: First, my code finds my find value, but does
not appear to meet my IF THEN criteria. Meaning it has added color to a
row that does not meet this criteria in my code... If rngFound.Offset(0,
-11).Value Sheets("data").Range("z4").Value Then "Color the row"... Is my
offset the wrong number? I have my find column in column U and the value I
am picking in the offset is in column J... isn't that offset(0,-11)?

The other thing is that when it finds and colors the first row in the loop,
it then ends the code, instead of continuing to find the rest of the rows
that meet the criteria.

Here is the code Thanks for the help!



Dim rngToSearch As Range
Dim wks As Worksheet
Dim rngFound As Range
Sheets("corps").Select
Call FindErrors
Range("u10").Select
Selection.EntireColumn.Hidden = False
Set wks = Sheets("corps")
Set rngToSearch = wks.Range("u11:u712")
Set rngFound = rngToSearch.find(what:="1", LookIn:=xlValues,
lookat:=xlWhole)

If rngFound.Offset(0, -11).Value Sheets("data").Range("z4").Value Then
saddr = rngFound.Address
r = rngFound.Row
Do
Range("a" & r, "s" & r).Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With

Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound Is Nothing Or rngFound.Address = saddr
Range("u10").Select
Selection.EntireColumn.Hidden = True
end if
end sub