macro to bold number of chars from end of a string

[herbwarri0r]

Hi all, (first time I've used a forum such as this)

I have a work book that produces a two page document by using
concatenate and information entered on the first worksheet. What I need
to do is make a number of chars in a string bold. I can't use;

With ActiveCell.Characters(Start:=118, Length:=8).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 12

As there is a varying length to the string due to the company name
being cancatenated in. Tried changing start to end but this did not
work. The characters I need bold are dates and will also be the only
numbers in the string.

I've also tried recording a macro to create the formula =RIGHT(B46,60)
to get the start of the text I need bold, paste it into a new cell and
format the needed chars to bold. Then all I need to do in insert the
test before in in that cell so to keep the formatting. This works but
the recoded macro uses the string of text when I need it to use the
cell reference for the company name.


=CONCATENATE("In the event of ",'Control Sheet'!E5,"'s"," failure to
remedy the Payment Breach We will refuse to accept any new orders for
the Service and/or suspend access to the Gateway on
",TEXT(Calender!AB5,"dd/mm/yyyy")," and/or exercise its right to
terminate the Contract and the Service provided under it at 12:00 on
",TEXT(Calender!AD5,"dd/mm/yyyy"),".")

So to surmise I need to paste the above to a new worksheet as values
and make the dates bold, but because the value in 'Control Sheet'!E5 is
going to be of a varying length I'm having trouble.


--
herbwarri0r

[robert111]

if cell A1 contains the text string "bob 18/1/06 fred 24/1/06 ****" you can search for a number between 0 and 9 , this will return the position of the various numbers within the string. Use the lowest number. LEN(A1) will give you the string length, can you work with this?

[herbwarri0r]

robert111 Wrote:
> if cell A1 contains the text string "bob 18/1/06 fred 24/1/06 ****"
> you
> can search for a number between 0 and 9 , this will return the
> position
> of the various numbers within the string. Use the lowest number.
> LEN(A1) will give you the string length, can you work with this?
>
>
> --
> robert111
> ------------------------------------------------------------------------
> robert111's Profile:
> http://www.excelforum.com/member.php...o&userid=31996
> View this thread:
> http://www.excelforum.com/showthread...hreadid=547812

Still a bit unsure of how I can do this in a macro. I can use;

ActiveCell = Application.SEARCH(0,ActiveCell,1)
ActiveCell = Application.SEARCH(1,ActiveCell,1)
ActiveCell = Application.SEARCH(2,ActiveCell,1)
ActiveCell = Application.SEARCH(3,ActiveCell,1) ....etc

But then how do I use the lowest number and use this to be the value of
Start in the following?

With ActiveCell.Characters(Start:=118, Length:=8).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 12

I also have the problem that each of my text strings has two dates
through the data. So the above method would be fine to make the first
date bold but I will have trouble making the second date bold. I
suppose there must be a way as each date uses 8 characters. So the
second date will have a start point of the ninth lowest value returned
from using Application.Search.


--
herbwarri0r

[Dave Peterson]

In your original post, you used a format of dd/mm/yyyy. In this example, you
lost the leading 0's in the day and only used two digit years.

This will work if you really use a format of dd/mm/yyyy. (But remember, you
have to be formatting values--not formulas--formulas can't use this kind of
character by character formatting.)

Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeConstants, xlTextValues))

On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No constants"
Exit Sub
End If

For Each myCell In myRng.Cells
For iCtr = 1 To Len(myCell.Value)
If Mid(myCell.Value, iCtr, 10) Like "##/##/####" Then
With myCell.Characters(Start:=iCtr, Length:=10).Font
.Name = "Arial"
.Bold = True
.Size = 12
End With
End If
Next iCtr
Next myCell
End Sub

herbwarri0r wrote:
>
> robert111 Wrote:
> > if cell A1 contains the text string "bob 18/1/06 fred 24/1/06 ****"
> > you
> > can search for a number between 0 and 9 , this will return the
> > position
> > of the various numbers within the string. Use the lowest number.
> > LEN(A1) will give you the string length, can you work with this?
> >
> >
> > --
> > robert111
> > ------------------------------------------------------------------------
> > robert111's Profile:
> > http://www.excelforum.com/member.php...o&userid=31996
> > View this thread:
> > http://www.excelforum.com/showthread...hreadid=547812
>
> Still a bit unsure of how I can do this in a macro. I can use;
>
> ActiveCell = Application.SEARCH(0,ActiveCell,1)
> ActiveCell = Application.SEARCH(1,ActiveCell,1)
> ActiveCell = Application.SEARCH(2,ActiveCell,1)
> ActiveCell = Application.SEARCH(3,ActiveCell,1) ....etc
>
> But then how do I use the lowest number and use this to be the value of
> Start in the following?
>
> With ActiveCell.Characters(Start:=118, Length:=8).Font
> Name = "Arial"
> FontStyle = "Bold"
> Size = 12
>
> I also have the problem that each of my text strings has two dates
> through the data. So the above method would be fine to make the first
> date bold but I will have trouble making the second date bold. I
> suppose there must be a way as each date uses 8 characters. So the
> second date will have a start point of the ninth lowest value returned
> from using Application.Search.
>
> --
> herbwarri0r

--

Dave Peterson

[herbwarri0r]

Dave Peterson Wrote:
> In your original post, you used a format of dd/mm/yyyy. In this
> example, you
> lost the leading 0's in the day and only used two digit years.
>
> This will work if you really use a format of dd/mm/yyyy. (But
> remember, you
> have to be formatting values--not formulas--formulas can't use this
> kind of
> character by character formatting.)
>
> Option Explicit
> Sub testme()
> Dim myRng As Range
> Dim myCell As Range
> Dim iCtr As Long
>
> Set myRng = Nothing
> On Error Resume Next
> Set myRng = Intersect(Selection, _
> Selection.Cells.SpecialCells(xlCellTypeConstants, xlTextValues))
>
> On Error GoTo 0
>
> If myRng Is Nothing Then
> MsgBox "No constants"
> Exit Sub
> End If
>
> For Each myCell In myRng.Cells
> For iCtr = 1 To Len(myCell.Value)
> If Mid(myCell.Value, iCtr, 10) Like "##/##/####" Then
> With myCell.Characters(Start:=iCtr, Length:=10).Font
> .Name = "Arial"
> .Bold = True
> .Size = 12
> End With
> End If
> Next iCtr
> Next myCell
> End Sub
> Dave Peterson

Thanks a lot! That worked brilliantly. Much appreaciated.


--
herbwarri0r