Hi All,

I am looking for a macro that will search a variable length column to find a
specific embedded character string (regardless of case, i.e., "Display",
"display", "DISPLAY" are the same) and then color code the found cell (any
color) and place an "X" in the cell one column to the right of the found cell
in the same row. Any help would be appreciated.

Wish list: I would like to set the macro in motion to search all
worksheets. Search column will always be the same on each worksheet. Will
only search one column.


Thank you,
Tony

You could have looked in vba help to find the basic code for findnext and
then modified as below.

Sub searchandmark()
For Each ws In Worksheets
With ws.Columns(6)
Set c = .Find("display", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = 6
c.Offset(0, 1) = "x"
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
Next ws
End Sub

--
Don Guillett
SalesAid Software

"Big Tony" wrote in message
...
Hi All,

I am looking for a macro that will search a variable length column to find
a
specific embedded character string (regardless of case, i.e., "Display",
"display", "DISPLAY" are the same) and then color code the found cell (any
color) and place an "X" in the cell one column to the right of the found
cell
in the same row. Any help would be appreciated.

Wish list: I would like to set the macro in motion to search all
worksheets. Search column will always be the same on each worksheet.
Will
only search one column.


Thank you,
Tony

Note that meeting the OP's requirements for case insensitivity and
matching an embedded string aren't guaranteed using the provided code.
One must explicitly set the LookAt and MatchCase arguments or settings
of previous searches could give the wrong results. Better:

Public Sub searchandmark()
Dim ws As Worksheet
Dim c As Range
Dim firstAddress As String
For Each ws In Worksheets
With ws.Columns(6)
Set c = .Find( _
What:="display", _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = 6
c.Offset(0, 1) = "x"
Set c = .FindNext(c)
Loop While c.Address < firstAddress
End If
End With
Next ws
End Sub


Note also that the "While Not c Is Nothing" in MS's example code:

Loop While Not c Is Nothing And c.Address < firstAddress

is superfluous - this loop only executes if c is Not Nothing and it
continues to loop around the search range - if c was found once,
FindNext will find it again.


In article ,
"Don Guillett" wrote:

You could have looked in vba help to find the basic code for findnext and
then modified as below.

Sub searchandmark()
For Each ws In Worksheets
With ws.Columns(6)
Set c = .Find("display", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = 6
c.Offset(0, 1) = "x"
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
Next ws
End Sub

Thanks Don. That works great. Next I tried removing the "For" clause to run
macro in one worksheet and keep getting error 424. I also removed "Next ws"

"Don Guillett" wrote:

You could have looked in vba help to find the basic code for findnext and
then modified as below.

Sub searchandmark()
For Each ws In Worksheets
With ws.Columns(6)
Set c = .Find("display", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = 6
c.Offset(0, 1) = "x"
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
Next ws
End Sub

--
Don Guillett
SalesAid Software

"Big Tony" wrote in message
...
Hi All,

I am looking for a macro that will search a variable length column to find
a
specific embedded character string (regardless of case, i.e., "Display",
"display", "DISPLAY" are the same) and then color code the found cell (any
color) and place an "X" in the cell one column to the right of the found
cell
in the same row. Any help would be appreciated.

Wish list: I would like to set the macro in motion to search all
worksheets. Search column will always be the same on each worksheet.
Will
only search one column.


Thank you,
Tony

JE.
I did test with Display,display,DISPLAY.

--
Don Guillett
SalesAid Software

"JE McGimpsey" wrote in message
...
Note that meeting the OP's requirements for case insensitivity and
matching an embedded string aren't guaranteed using the provided code.
One must explicitly set the LookAt and MatchCase arguments or settings
of previous searches could give the wrong results. Better:

Public Sub searchandmark()
Dim ws As Worksheet
Dim c As Range
Dim firstAddress As String
For Each ws In Worksheets
With ws.Columns(6)
Set c = .Find( _
What:="display", _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = 6
c.Offset(0, 1) = "x"
Set c = .FindNext(c)
Loop While c.Address < firstAddress
End If
End With
Next ws
End Sub


Note also that the "While Not c Is Nothing" in MS's example code:

Loop While Not c Is Nothing And c.Address < firstAddress

is superfluous - this loop only executes if c is Not Nothing and it
continues to loop around the search range - if c was found once,
FindNext will find it again.


In article ,
"Don Guillett" wrote:

You could have looked in vba help to find the basic code for findnext
and
then modified as below.

Sub searchandmark()
For Each ws In Worksheets
With ws.Columns(6)
Set c = .Find("display", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = 6
c.Offset(0, 1) = "x"
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
Next ws
End Sub

Sub searchandmarkoneWS()

with columns(6)
Set c = .Find("display", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = 6
c.Offset(0, 1) = "x"
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End Sub


--
Don Guillett
SalesAid Software

"Big Tony" wrote in message
...
Thanks Don. That works great. Next I tried removing the "For" clause to
run
macro in one worksheet and keep getting error 424. I also removed "Next
ws"

"Don Guillett" wrote:

You could have looked in vba help to find the basic code for findnext
and
then modified as below.

Sub searchandmark()
For Each ws In Worksheets
With ws.Columns(6)
Set c = .Find("display", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = 6
c.Offset(0, 1) = "x"
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
Next ws
End Sub

--
Don Guillett
SalesAid Software

"Big Tony" wrote in message
...
Hi All,

I am looking for a macro that will search a variable length column to
find
a
specific embedded character string (regardless of case, i.e.,
"Display",
"display", "DISPLAY" are the same) and then color code the found cell
(any
color) and place an "X" in the cell one column to the right of the
found
cell
in the same row. Any help would be appreciated.

Wish list: I would like to set the macro in motion to search all
worksheets. Search column will always be the same on each worksheet.
Will
only search one column.


Thank you,
Tony

Actually, yours is best depending on the prior search being different. So,
looking at part and match case false would be better.

--
Don Guillett
SalesAid Software

"JE McGimpsey" wrote in message
...
Note that meeting the OP's requirements for case insensitivity and
matching an embedded string aren't guaranteed using the provided code.
One must explicitly set the LookAt and MatchCase arguments or settings
of previous searches could give the wrong results. Better:

Public Sub searchandmark()
Dim ws As Worksheet
Dim c As Range
Dim firstAddress As String
For Each ws In Worksheets
With ws.Columns(6)
Set c = .Find( _
What:="display", _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = 6
c.Offset(0, 1) = "x"
Set c = .FindNext(c)
Loop While c.Address < firstAddress
End If
End With
Next ws
End Sub


Note also that the "While Not c Is Nothing" in MS's example code:

Loop While Not c Is Nothing And c.Address < firstAddress

is superfluous - this loop only executes if c is Not Nothing and it
continues to loop around the search range - if c was found once,
FindNext will find it again.


In article ,
"Don Guillett" wrote:

You could have looked in vba help to find the basic code for findnext
and
then modified as below.

Sub searchandmark()
For Each ws In Worksheets
With ws.Columns(6)
Set c = .Find("display", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = 6
c.Offset(0, 1) = "x"
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
Next ws
End Sub

In article ,
"Don Guillett" wrote:

I did test with Display,display,DISPLAY.

Hmm... Since the LookAt and LookIn arguments do get set, I didn't look
at the MatchCase argument - but on my current version, too, MatchCase
seems to be reset to False unless explicitly specified.

Don't know whether that's a bug in XL or an error in Help, but I'll bug
it to MS.

Thanks to both of you. Don I used your one sheet option and included the
missing "End With". I got the desired results using both your code and JE's.

Thanks again,
Tony

"Don Guillett" wrote:

Actually, yours is best depending on the prior search being different. So,
looking at part and match case false would be better.

--
Don Guillett
SalesAid Software

"JE McGimpsey" wrote in message
...
Note that meeting the OP's requirements for case insensitivity and
matching an embedded string aren't guaranteed using the provided code.
One must explicitly set the LookAt and MatchCase arguments or settings
of previous searches could give the wrong results. Better:

Public Sub searchandmark()
Dim ws As Worksheet
Dim c As Range
Dim firstAddress As String
For Each ws In Worksheets
With ws.Columns(6)
Set c = .Find( _
What:="display", _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = 6
c.Offset(0, 1) = "x"
Set c = .FindNext(c)
Loop While c.Address < firstAddress
End If
End With
Next ws
End Sub


Note also that the "While Not c Is Nothing" in MS's example code:

Loop While Not c Is Nothing And c.Address < firstAddress

is superfluous - this loop only executes if c is Not Nothing and it
continues to loop around the search range - if c was found once,
FindNext will find it again.


In article ,
"Don Guillett" wrote:

You could have looked in vba help to find the basic code for findnext
and
then modified as below.

Sub searchandmark()
For Each ws In Worksheets
With ws.Columns(6)
Set c = .Find("display", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Interior.ColorIndex = 6
c.Offset(0, 1) = "x"
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
Next ws
End Sub