Using Find and FindNext to Format Cell Text

[spovolny]

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

 

[Guest]

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

 

[spovolny]

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

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