Code glitches

  • Thread starter Thread starter 2D Rick
  • Start date Start date
2

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
 
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.
 
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)
 
FINDNEXT
Don said:
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.
Click to expand...
 
FINDNEXT worked perfect, thanks Don
Rick

Don said:
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.
Click to expand...
 
FINDNEXT worked perfect, thanks Bob
Rick

Bob said:
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)
Click to expand...
 
Back
Top