Formatting (2002)

Topic

New Reply

I have a formula in a cell that uses a named range like this:

=”Error with vendor # – ” & VendorCode

Is there a way to make the data represented by the VendorCode named range to be bold faced so that it stands out?

Reply | Quote

Replies

No, that is not possible, you can only format individual characters in constant values, not in the result of a formula.
You could use 2 neighboring cells, one containing the text Error with vendor # – and one with the formula =VendorCode. You can then format the second cell as bold.

Reply | Quote

Thanks for the quick response. I can’t think of a way for VBA to help me either. Putting it in two cells would work but the alignment with the rest of the spreadsheet would be messed up. Seems like I will have to do without it. Thanks again.

Reply | Quote

If you are going to use VB, an option would be to convert the formula to a value, and then have VB convert the appropriate chars to bold.

Your macro would have to update the cell as appropriate with changes to the value since you would no longer have a formula.

Steve

Reply | Quote

Thanks Steve. I could do a copy and paste values only into another cell. How would I go about converting certain characters I want to bold face?

Reply | Quote

Here is an example. You can add this to the workbook object in VB of the appropriate sheet (not a regular module):

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim str1 As String
    Dim str2 As String
    If Not Intersect(Target, Range("VendorCode")) Is Nothing Then
        str1 = "Error with Vendor # - "
        str2 = Range("VendorCode").Value
        Application.EnableEvents = False
        With Range("a1")
            .Value = str1 & str2
            .Characters(Start:=Len(str1) + 1, Length:=Len(str2)). _
                Font.FontStyle = "Bold"
        End With
        Application.EnableEvents = True
    End If
End Sub

When vendor code is changed, the code runs and changes the contents of cell A1 (change as desired)

Steve

Reply | Quote

Hi Steve, I was able to get back to this and apply your example. That character property start and length parameters really helped and it’s working great now. Thanks for your time and help!

Reply | Quote

I see that Steve has replied with code that is similar to what is in the workbook in my reply. However, I think that what you were asking in this question was how to format the vendor code as bold manually after you Paste Special the string into a cell. You would do this by selecting the cell, and then select the text that you want to format in the formula bar. Then either click on the formatting you want in the toolbar, or select Cells from the Format menu and select the formatting you want.

Reply | Quote

I didn’t see your post or I would not have written the code…

Steve

Reply | Quote

No problem there, I was more concerned that it looked like you misunderstood what the poster was asking. Better to have too much code than too little.

Reply | Quote

I might have misunderstood. I don’t know if he had a manual way in mind or via code. I was leaning towards code since he wanted it in a formula.

Steve

Reply | Quote

Since he was talking about copying the cell with the formula and doing a Paste Special/Values, I thought he was talking about doing it manually.

Reply | Quote

Sorry for the delay. Got pulled off onto something else. I was thinking of copying and pasting values via VBA code and applying the format. Steve understood what I was trying to do and his example worked out real well. Thanks for your input too!

Reply | Quote

Legare also understood. His example code (with a spreadsheet) is almost the same as mine and was posted first. His manual way was a followup answer thinking you were doing it manually.

Steve

Reply | Quote

You’re right. It was just that I found your post before I discovered Legare’s attachment. But I just wanted to answer his last post as to what it was I was asking, VBA or manually. You guys are just so quick it’s hard to keep up.

Reply | Quote

Yes I missed his post also or I would not have posted my code.

I commented on this since you implied I understood and he did not

Steve

Reply | Quote

Yes, Legare did have it right. Sorry, I didn’t mean to imply that he didn’t. His last post ended with, “…I thought he was talking about doing it manually.” I was just clarifying that it was not manually but with VBA. I appreciate the attachment that Legare put together to help me out.

Reply | Quote

Not directly. Depending on whether the cell represented by the name VendorCode contains a constant or a formula, you could use the worksheet change event routine or the worksheet calculate routine to build the string as a constant, put it in the target cell, and format it however you want.

The attached worksheet shows how it would work with a constant in A1 and a formula in A2 and the results in D1 and D2 respectivly.

Reply | Quote