highlight cell text

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
 
B

Bob Phillips

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)
 
G

Guest

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
 
T

Tom Ogilvy

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.
 
J

jim briers

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



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
 
J

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
 
B

Bob Phillips

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)
 
B

Bob Phillips

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)
 
Top