Solved excel bold text in formula

February 10, 2011 at 09:58:10
Specs: Windows XP
I have a simple If condition, but I want the result to be partially bolded and I dont know how

=IF(K21=0,"7. Offset with 0875 2071020059 012167 DIFF","7. Offset with 0875 1879902642 012167 DIFF")

I want it to show "7. Offset with 0875 2071020059 012167 DIFF" or "7. Offset with 0875 1879902642 012167 DIFF" were "7" & "DIFF" are bold

Any advice? Thanks


See More: excel bold text in formula

Report •


✔ Best Answer
February 10, 2011 at 18:51:35
re: "The amount in questions gets taken out of cell K21

I assume that means the K21 will either be 0 or not be zero. That's what your IF statement implies, so that's what the code checks for.

re: "The results gets shown on cell R27"

I changed the references to my example of A1 to be R27.

BTW I also changed the comment lines from K1 to K21, but that was just a typo that won't matter since comment lines don't get executed.

Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False
'Was change made to K21?
   If Target.Address = "$K$21" Then
'Place string if K21 = 0
      If Target = 0 Then
        Range("R27") = _
          "7. Offset with 0875 2071020059 012167 DIFF"
      Else
'Place string if K21 <> 0
        Range("R27") = _
          "7. Offset with 0875 1879902642 012167 DIFF"
      End If
'Format R27
    With Range("R27")
        .Font.Bold = False
        .Characters(1, 1).Font.Bold = True
        .Characters(Len(Range("R27")) - 3, 4).Font.Bold = True
    End With
   End If
 Application.EnableEvents = True
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
February 10, 2011 at 12:17:43
You can't bold the "partial result" of your formula because the text isn't really in the cell, the formula is.

However, if you use VBA to place the actual text string in the cell, then you can use VBA to format whichever characters you want.

For example, if you were planning to use that formula in A1, then this code will monitor K1 and place the correct value in A1 and then format it.

Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False
'Was change made to K1?
   If Target.Address = "$K$21" Then
'Place string if K1 = 0
      If Target = 0 Then
        Range("A1") = _
          "7. Offset with 0875 2071020059 012167 DIFF"
      Else
'Place string if K1 <> 0
        Range("A1") = _
          "7. Offset with 0875 1879902642 012167 DIFF"
      End If
'Format A1
    With Range("A1")
        .Font.Bold = False
        .Characters(1, 1).Font.Bold = True
        .Characters(Len(Range("A1")) - 3, 4).Font.Bold = True
    End With
   End If
 Application.EnableEvents = True
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 10, 2011 at 12:36:17
Thanks for the reply... any good tutorials on VBA??? : /

Report •

#3
February 10, 2011 at 13:38:43
Ok I think I get the idea... but I cant get to the correct code

Lets get specific: 1) the sheet is called "Retail FLat Cancel"
2) The amount in questions gets taken out of cell K21
3) The results gets shown on cell R27

Im having problems starting and ending the code... dont know if Im clear... probably not.


Report •

Related Solutions

#4
February 10, 2011 at 18:51:35
✔ Best Answer
re: "The amount in questions gets taken out of cell K21

I assume that means the K21 will either be 0 or not be zero. That's what your IF statement implies, so that's what the code checks for.

re: "The results gets shown on cell R27"

I changed the references to my example of A1 to be R27.

BTW I also changed the comment lines from K1 to K21, but that was just a typo that won't matter since comment lines don't get executed.

Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False
'Was change made to K21?
   If Target.Address = "$K$21" Then
'Place string if K21 = 0
      If Target = 0 Then
        Range("R27") = _
          "7. Offset with 0875 2071020059 012167 DIFF"
      Else
'Place string if K21 <> 0
        Range("R27") = _
          "7. Offset with 0875 1879902642 012167 DIFF"
      End If
'Format R27
    With Range("R27")
        .Font.Bold = False
        .Characters(1, 1).Font.Bold = True
        .Characters(Len(Range("R27")) - 3, 4).Font.Bold = True
    End With
   End If
 Application.EnableEvents = True
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question