• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Format part of the text in cell with formula

W

wrybel

New Member
Hi,

I have a text in a cell C1, which consists of values from 2 cells and a sign =A1&"/"&B1. I would like to bold the first part of the text in C1 like 12/15. Could any one share with me a VBA code ?

Thanks !
 

Hi,

not possible within a formula, ok with text only
as you can see yourself using Macro recorder …
​
 
try this

Code: 
Sub test()

With Range("c1").Characters(Start:=1, Length:=2).Font
  .FontStyle = "bold"
End With

End Sub
it is basic...if you want to use it specific range for downwards let me know...
 
Last edited by a moderator:
ANKUSHRS1,

your's solution works only in there is a text in C1 not a formula. Do you know how to fix it ?
 

Burning a couple of neurons, you should see you do not need a code !
Just format cell A1 in bold and type a slash in B1
or just format cell B1 to display value starting with a slash …
​
 
Marc L,

in A1 I have a number 12 in B1 number 15 in C1 I would like to have a dynamic cell with formula =A1&"/"&B1. I can't use your solution.
 
What's the purpose of cell C1 ?

Anyway as it could be achieved by a beginner (ANKUSHRS1 if you wanna try)
and I must move away from computer for hours,
you could use the Worksheet_Change event
to concatenate both cells and apply bold font at beginning …
​
 
Hi ,

See if this helps :
Code: 
Sub test()
    With ActiveCell
         s = Application.Evaluate(.Formula)
         .NumberFormat = "@"
         .Value = s
         .Characters(Start:=1, Length:=2).Font.FontStyle = "bold"
    End With
End Sub
Narayan
 
Narayan,

this is great ! Could you please add 2 more features.

1. not to change particular cell but range eg C1:C5

2. bold only number befor "/" eg. 9/15 123/200
 
based on #13

Code: 
Option Explicit

Sub test()
Dim r As Range, s As Variant
For Each r In [C1:C5]
    With r
        s = Application.Evaluate(.Formula)
        .NumberFormat = "@"
        .Value = s
        .Characters(Start:=1, Length:=InStr(s, "/") - 1).Font.FontStyle = "bold"
    End With
Next
End Sub
 
​
Paste this code to the worksheet module :​
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
        Dim Rg As Range, L&
        Application.EnableEvents = False
    For Each Rg In Intersect(Target, [A1:C5]).Rows
        L = Cells(Rg.Row, 2).Value > ""
   With Cells(Rg.Row, 3)
       .Font.Bold = False
       .Value = IIf(L, "'", "") & Cells(Rg.Row, 1).Value & IIf(L, "/" & Cells(Rg.Row, 2).Text, "")
        L = Len(Cells(Rg.Row, 1).Text)
        If L Then .Characters(1, L).Font.Bold = True
   End With
    Next
        Application.EnableEvents = True
End Sub
Do you like it ? So thanks to click on bottom right Like !​
​
 
You must log in or register to reply here.
Back
Top