PDA

View Full Version : Sleeper: Concetenate with bold letter for one line



deemas
06-26-2005, 01:01 AM
i want to add three coulms data (text+number) to one cell line by line, i have use the function as follows

CONCATENATE(A2,Char(10),B2,Char(10),C2), it is working fine, More over i want the c2 part to to be Bold letter?

Can any one help me to write function to get Bold letter in last line of result cell.

Thanks in advance.

Bob Phillips
06-26-2005, 02:07 AM
i want to add three coulms data (text+number) to one cell line by line, i have use the function as follows

CONCATENATE(A2,Char(10),B2,Char(10),C2), it is working fine, More over i want the c2 part to to be Bold letter?

Can any one help me to write function to get Bold letter in last line of result cell.

Thanks in advance.

I don't think this will work, as a function can return a result to a worksheet, but not change attributes of the worksheet or its cells.

deemas
06-26-2005, 02:18 AM
THANK YOU FOR YOUR REPLY!

IS THERE ANYWAY USING FORMAT FUNCTION LIKE FORMAT(C2, [BOLD]), OR NO WAY TO WRITE A CUSTOM FUNCTION FOR BOLD THE CONTENT.

mdmackillop
06-26-2005, 03:21 AM
Hi Deemas,
You can certainly do this as a sub, but I can't at the moment, think of a solution as a function


Sub Formats()
Dim MyTest As String
Dim Len1 As Long, Len2 As Long
mytext = ActiveCell.Offset(0, -3) & Chr(10) & ActiveCell.Offset(0, -2) _
& Chr(10) & ActiveCell.Offset(0, -1)
Len1 = Len(mytext)
Len2 = Len(ActiveCell.Offset(0, -1))
ActiveCell.Formula = mytext
With ActiveCell.Characters(Start:=Len1 - Len2 + 1, Length:=Len2).Font
.FontStyle = "Bold"
End With
End Sub

johnske
06-26-2005, 07:35 AM
You mean like this? (modifying what MD's already done)


Option Explicit

Sub DoIt()
SpclFormat ActiveCell, "Bold"
End Sub

Function SpclFormat(Rng As Range, FontType As String)
Dim MyText As String
Dim Len1 As Long, Len2 As Long
MyText = Rng.Offset(0, -3) & Chr(10) & Rng.Offset(0, -2) _
& Chr(10) & Rng.Offset(0, -1)
Len1 = Len(MyText)
Len2 = Len(Rng.Offset(0, -1))
Rng.Formula = MyText
With Rng.Characters(Start:=Len1 - Len2 + 1, Length:=Len2).Font
.FontStyle = FontType
End With
End Function

deemas
06-26-2005, 10:36 PM
Thank you very much for your replies,

But it is giving Name eror. May be i have describe in wrong way.

Please check the attached file, where i have put the result field which i lokking for.


Thanks,

mdmackillop
06-27-2005, 12:24 AM
For your data as shown, try the following. I have assumed it is in columns A:C, and the code should be adjusted if this is not the case


Sub Formats()
Dim MyTest As String
Dim Len1 As Long, Len2 As Long
Dim i As Long
Application.ScreenUpdating = False
For i = [A65536].End(xlUp).Row To 2 Step -1
If Range("A" & i) <> "" Then
With Range("A" & i)
mytext = .Offset(0, 0) & Chr(10) & .Offset(0, 1) _
& Chr(10) & .Offset(0, 2)
Len1 = Len(mytext)
Len2 = Len(.Offset(0, 2))
.Offset(0, 3).Formula = mytext
With .Offset(0, 3).Characters(Start:=Len1 - Len2 + 1, Length:=Len2).Font
.FontStyle = "Bold"
.Underline = xlUnderlineStyleSingle
End With
End With
End If
Next
Columns("A:C").VerticalAlignment = xlCenter
Application.ScreenUpdating = True
End Sub

deemas
06-27-2005, 01:33 AM
Thanks a lot, it works fine.

It is really a great affort for me from your side. it save me lot.

More over i was expecting just a Fuction to Bold a cell value (what ever it is) to include in concetenate function for particular line (cell). then it can be use in any where in the sheet.

Any how i got finish my project with your great help...

Thanks,,,

Bob Phillips
06-27-2005, 01:56 AM
Thank you very much for your replies,

But it is giving Name eror. May be i have describe in wrong way.

Please check the attached file, where i have put the result field which i lokking for.

I would do it as a selction event



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iPos As Long
Dim i As Long
With Target
If .Column = 4 And .Row > 1 Then
.Value = .Offset(0, -3) & Chr(10) & .Offset(0, -2) & _
Chr(10) & .Offset(0, -1)
iPos = Len(.Offset(0, -3) & Chr(10) & .Offset(0, -2) & Chr(10))
With .Characters(iPos, Len(.Offset(0, -1)) + 1).Font
.FontStyle = "Bold"
.Underline = xlUnderlineStyleSingle
End With
End If
End With
End Sub


This is worksheet event code.

mdmackillop
06-27-2005, 05:51 AM
Hi Deemas,
Having looked closely at it, I don't believe it is possible to include the formatting as part of a user defined function.

deemas
06-27-2005, 05:57 AM
:beerchug: Thanks lot again mdmackillop,

Fine, your previous code is working for me. i was just trying for fucntion.

I love this Forum,

Great piece of work :clap:


Thanks,

Deemas

Bob Phillips
06-27-2005, 06:04 AM
Hi Deemas,
Having looked closely at it, I don't believe it is possible to include the formatting as part of a user defined function.

part of the problem to overcome is that the cell would contain the formula when parsed, not the result.