CODEWITHSUNDEEP
excelvba
qlDavid
qlDavid

Reputation: 23

Change part of a cell to bold

I have a column with cells that cointans only numbers. If the length of the number in the cell is 5, I want to add 6 in front of the number and 00 at the end of it.
This works so far, but know I also want to make the original number bold but not the 6 and 00. I tried to turn the whole column bold and then add the 6 and 00, but that didnt work.

My following code is not working correct with numbers. It only changes the font to bold when I set the Start to 1, but then it changes the whole cell instead of the 5 numbers. If I replace the numbers with a string it's working fine, so I tried to convert the number to a string, but that didn't work aswell.

Dim c As Range
For Each c In Range("A1:A500").Cells
    If Len(c.Value) = 5 Then
        c.Value = "6" & c.Value & "00"
        c.Value = CStr(c.Value)
        c = CStr(c.Value)
        c.Characters(Start:=2, Length:=5).Font.Bold = True 
    End If
Next

Any help would be appreciated!

Upvotes: 2

Views: 374

Answers (2)

Vityata
Vityata

Reputation: 43565

The problem is that Excel thinks by default that the numbers should be formatted numerically. And it does not provide the possibility to bold digits of a number. Unless, the number is converted to a text, but then it is a text. By default, numbers are aligned to the right, text is aligned to left:

enter image description here

There are 2 basic ways to convert a number to text in Excel:

  • By explicit conversion - select the number, press Ctrl + 1, select text. Then record a macro to see the generated code;
  • By adding a string to the number - e.g. from 12345 make a12345 (But this changes the value a bit)
Sub TestMe()

    Dim c As Range
    For Each c In Range("A1:A5").Cells
        If Len(c.Value) = 5 Then
            c.NumberFormat = "@"
            c.Value = "6" & c.Value & "00"
            c.Characters(Start:=3, Length:=5).Font.Bold = True
        End If
    Next

End Sub

Upvotes: 2

Scott Holtzman
Scott Holtzman

Reputation: 27239

Formatting c as a text will solve it.

c.NumberFormat = "@"

Or you can directly format the range before running the code.

Upvotes: 3

Related Questions