highlight cell text

  • Thread starter Thread starter jim
  • Start date Start date
J

jim

I have an excel spreadsheet that I have to update every month. This
involves laboriously highlighting all text between the characters "<"
and ">" in one particular column. Is it possible to write a macro
that will make my life easier?! I'm new to this so all pointers would
be really appreciated.

MTIA

Jim
 
Jim,

Here is one way using conditional formatting.

Select all the cells in the column (I am assuming column A).
Goto to Format>Conditional Formatting
Set Condition 1 to Formula Is
Add this formula
=AND(ROW(A1)<=MATCH(">",$A$1:$A$100,0),ROW(A1)>=MATCH("<",$A$1:$A$100,0))
Click format, and set a pattern colour
OK out

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Dim ColToCheck as range
Dim cl as range,Temp as string

set ColToCheck=range(cells(1,1),cells(1,1).end(xldown))

for each cl in ColToCheck
 
do you mean some cells contain a text string like

the object of her desire < Brian Crumply > was new to the club.

Assuming yes,

You would want Brian Crumply highighted with color? bolding? Italics?
different Font/Size?

Could there be multiple < > pairs in a single cell.
 
Hi Bob

I thought that would solve my problem but unfortunately it didn't work.
Just to clarify I have cells with various text in e.g. the car whent
through <span class="five">the mud </span>. I need to be able to
highlight the html parts of the string.

Many thanks

jim
 
Hi Bob

I thought that would solve my problem but unfortunately it didn't work.
Just to clarify I have cells with various text in e.g. the car went
through <span class="five">the mud </span>. I need to be able to
highlight the html parts of the string.

Many thanks

jim
 
Sorry, I read it that the < was in a cell marking a start point, > in
another marking the end point.

Here's some VBA that should do it. Select all the cells, then run this macro

Sub HighlightText()
Dim cell As Range
Dim i As Long
Dim iStart As Long
Dim iEnd As Long

For Each cell In Selection
i = 1
Do
iStart = InStr(i, cell.Value, "<")
If iStart > 0 Then
iEnd = InStr(iStart + 1, cell.Value, ">")
If iEnd < 1 Then
iEnd = Len(cell.Value) + 1
End If
With cell.Characters(iStart + 1, iEnd - 1 - iStart).Font
.Bold = True
.ColorIndex = 3
End With
End If
i = iEnd + 1
Loop Until iStart < 1
Next cell
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Pleasure. Took a while to sink in, but I got it I the end.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top