Code glitches

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

2D Rick - 07 Jan 2007 14:33 GMT

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

Reply to this Message

Don Guillett - 07 Jan 2007 14:52 GMT

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.

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

>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.
[quoted text clipped - 29 lines]
>
> End Sub

Reply to this Message

2D Rick - 07 Jan 2007 20:44 GMT

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.
[quoted text clipped - 38 lines]
> >
> > End Sub

Reply to this Message

2D Rick - 07 Jan 2007 20:58 GMT

FINDNEXT worked perfect, thanks Don
Rick

Reply to this Message

Bob Phillips - 07 Jan 2007 15:14 GMT

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

Signature

---
HTH

Bob

(change the xxxx to gmail if mailing direct)

>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.
[quoted text clipped - 29 lines]
>
> End Sub

Reply to this Message

2D Rick - 07 Jan 2007 21:01 GMT

FINDNEXT worked perfect, thanks Bob
Rick

> Dim cell As Range
> Dim sFirst As String
[quoted text clipped - 62 lines]
> >
> > End Sub

Reply to this Message