ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   highlight cell text (https://www.excelbanter.com/excel-programming/291264-highlight-cell-text.html)

jim

highlight cell text
 
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

Bob Phillips[_6_]

highlight cell text
 
Jim,

Here is one way using conditional formatting.

Select all the cells in the column (I am assuming column A).
Goto to FormatConditional Formatting
Set Condition 1 to Formula Is
Add this formula
=AND(ROW(A1)<=MATCH("",$A$1:$A$100,0),ROW(A1)=MA TCH("<",$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)

"jim" wrote in message
m...
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




No Name

highlight cell text
 
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



-----Original Message-----
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
.


Tom Ogilvy

highlight cell text
 
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.

--
Regards,
Tom Ogilvy


"jim" wrote in message
m...
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 briers

highlight cell text
 
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!

jim

highlight cell text
 
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


"Bob Phillips" wrote in message ...
Jim,

Here is one way using conditional formatting.

Select all the cells in the column (I am assuming column A).
Goto to FormatConditional Formatting
Set Condition 1 to Formula Is
Add this formula
=AND(ROW(A1)<=MATCH("",$A$1:$A$100,0),ROW(A1)=MA TCH("<",$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)

"jim" wrote in message
m...
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


Bob Phillips[_6_]

highlight cell text
 
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)

"jim briers" wrote in message
...
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!




jim briers

highlight cell text
 
Works like a dream Bob!

Thanks very much

Jim



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

Bob Phillips[_6_]

highlight cell text
 
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)

"jim briers" wrote in message
...
Works like a dream Bob!

Thanks very much

Jim



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





All times are GMT +1. The time now is 10:58 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com