Using Find and FindNext to Format Cell Text
 

Hi -
I know this is a simple question, but I'm looking for a way to find
all instances of a text in a single column - and format the first
found text color as black and all the remaining as white. I would
really appreciate a quick rundown on how to do this - thanks for all
you guys do.

Steve

Using Find and FindNext to Format Cell Text
 

Hi Steve

Here's the full code I wrote for you. Only 1 thing to change, see in the
code where.

Sub colouring()

Dim rngTextCells As Range
Dim lFirstRow As Long

With Columns("A") 'adjust to suit

On Error Resume Next
Set rngTextCells = .SpecialCells(xlCellTypeFormulas, 2)
lFirstRow = rngTextCells.Row
Set rngTextCells = Application.Union(rngTextCells,
..SpecialCells(xlCellTypeConstants, 2))
On Error GoTo 0

End With

If Not rngTextCells Is Nothing Then

rngTextCells.Font.ColorIndex = 2
lFirstRow = WorksheetFunction.Min(lFirstRow, rngTextCells.Row)
Cells(lFirstRow, rngTextCells.Column).Font.ColorIndex = 1

End If

End Sub


--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


" wrote:

Hi -
I know this is a simple question, but I'm looking for a way to find
all instances of a text in a single column - and format the first
found text color as black and all the remaining as white. I would
really appreciate a quick rundown on how to do this - thanks for all
you guys do.

Steve

Using Find and FindNext to Format Cell Text
 

On Jul 16, 5:58 pm, Wigi wrote:
Hi Steve

Here's the full code I wrote for you. Only 1 thing to change, see in the
code where.

Sub colouring()

Dim rngTextCells As Range
Dim lFirstRow As Long

With Columns("A") 'adjust to suit

On Error Resume Next
Set rngTextCells = .SpecialCells(xlCellTypeFormulas, 2)
lFirstRow = rngTextCells.Row
Set rngTextCells = Application.Union(rngTextCells,
.SpecialCells(xlCellTypeConstants, 2))
On Error GoTo 0

End With

If Not rngTextCells Is Nothing Then

rngTextCells.Font.ColorIndex = 2
lFirstRow = WorksheetFunction.Min(lFirstRow, rngTextCells.Row)
Cells(lFirstRow, rngTextCells.Column).Font.ColorIndex = 1

End If

End Sub

--
Wigihttp://www.wimgielis.be= Excel/VBA, soccer and music

" wrote:
Hi -
I know this is a simple question, but I'm looking for a way tofind
all instances of a text in a single column - and format the first
found text color as black and all the remaining as white. I would
really appreciate a quick rundown on how to do this - thanks for all
you guys do.

Steve

Hey Wigi -
I implemented your code - and the correct column was A - it executed
successfully but nothing changed. I have multiple instances of the
same name in this column, and hypothetically the first one should have
been colored black, and all the rest white. Is this what the code was
meant to do? Thanks alot for your help.
Steve