Code glitches

[2D Rick]

I want to look at a range of cells and find "AGT".
When a cell containing "AGT" is found I want to Highlite it in yellow.

The below code doesn't find all the cells.
I have bumped intCount up to 1000 and this is no help.

Help, Rick

Sub Macro3()

Dim intCount As Integer
Dim x

intCount = Application.CountIf(Range("B1:F13"), "AGT")

For x = 1 To intCount

Cells.Find(What:="AGT", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate


ActiveCell.Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With

Cells.FindNext(After:=ActiveCell).Activate
Next

End Sub

 

[Don Guillett]

Your 1 to intcount is only counting the NUMBER of AGT's . Try looking in VBA
Help for FINDNEXT and changing the example given instead of your effort.
Also you do NOT need to make any selections. Post back if you can't figure
it out.

 

[Bob Phillips]

Dim cell As Range
Dim sFirst As String

With Range("B1:F13")

Set cell = .Find(What:="AGT", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not cell Is Nothing Then
sFirst = cell.Address
Do
cell.Interior.ColorIndex = 6
cell.Interior.Pattern = xlSolid
Set cell = Cells.FindNext(cell)
Loop While Not cell Is Nothing And sFirst <> cell.Address
End If

End With

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[2D Rick]

FINDNEXT

Your 1 to intcount is only counting the NUMBER of AGT's . Try looking in VBA
Help for FINDNEXT and changing the example given instead of your effort.
Also you do NOT need to make any selections. Post back if you can't figure
it out.

 

[2D Rick]

FINDNEXT worked perfect, thanks Don
Rick

Your 1 to intcount is only counting the NUMBER of AGT's . Try looking in VBA
Help for FINDNEXT and changing the example given instead of your effort.
Also you do NOT need to make any selections. Post back if you can't figure
it out.

 

[2D Rick]

FINDNEXT worked perfect, thanks Bob
Rick

Dim cell As Range
Dim sFirst As String

With Range("B1:F13")

Set cell = .Find(What:="AGT", _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not cell Is Nothing Then
sFirst = cell.Address
Do
cell.Interior.ColorIndex = 6
cell.Interior.Pattern = xlSolid
Set cell = Cells.FindNext(cell)
Loop While Not cell Is Nothing And sFirst <> cell.Address
End If

End With

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)