Formatting Half of the Cell data in formula

[gnaga]

Hi,

I have a formula in Cell C1 "=Text(A1,"0.00") & $B$1"

I have a number in A1 and a Text in B1

While displaying this in C1, I would like to have bigger font for the number and smaller font for the text in order to accommodate within the width of cell C1.

How to achieve this task? Kindly help me.

TIA
GNAGA

 

[Fluff]

Unfortunately I do not believe that is possible.

 

[gnaga]

Thanks Fluff. You are MVP so it must be true that what I ask is not achievable in the current excel. May be possible in future. Thanks again.

 

[Fluff]

Whilst I am an MVP on this site, that does not mean I know everything (far from it).
Somebody else may come along & prove me wrong.

 

[jasonb75]

You can apply such formatting manually with a text string, but not with a formula.

It might be possible to create a workaround with vba to replace the formula with the result as a text string, then apply the formatting, but I suspect that this wil create more problems than it solves.

If you align the content of A1 to the right of the cell and B1 to the left, with the right formatting they should look like they are joined in a single cell.

Formatting the cells with a white fill colour and selected borders using line colour 'White, Background 1, 15% Darker' will blend in with the normal grid lines on the sheet. The only thing to remember is that borders print, grid lines do not print by default.

 

[Rick Rothstein]

Unfortunately I do not believe that is possible.

You are correct... you cannot format parts of a cell differently from the rest of the cell if the cell contains a formula, you can only do that if the cell contains a constant.

It might be possible to create a workaround with vba to replace the formula with the result as a text string, then apply the formatting, but I suspect that this will create more problems than it solves.

The formula is simple enough so it would be easy enough to create an event procedure to place text in the cell that is identical to what that formula would display... I don't think doing so would create any additional problems on the sheet. To write such an event procedure, the OP would need to tell us the starting cell for his formula and the sizes to make the numerical and text sizes.

 

[jasonb75]

Rick, when I said that I was thinking that a calculate event would format the existing results but not pick up any future changes, while a change event would do the opposite.

Thought I would suggest an alternative that the OP might consider before attempting 2 events that might not be necessary.

 

[Rick Rothstein]

Rick, when I said that I was thinking that a calculate event would format the existing results but not pick up any future changes, while a change event would do the opposite.

I was thinking of a Change event that was keyed to the cells in Columns A and B, the cells the formula was dependent on.

 

[Rick Rothstein]

I was thinking of a Change event that was keyed to the cells in Columns A and B, the cells the formula was dependent on.

This was the code I had in mind (figured I might as well get a jump on this for when the OP comes back)...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Rw As Long, Txt As String, Rng As Range, Cell As Range
  Const AssumedNumberSize As Long = 14
  Const AssumedTextSize As Long = 10
  Set Rng = Intersect(Target, Columns("A:B"))
  If Not Rng Is Nothing Then
    Application.EnableEvents = False
    For Each Cell In Rng
      Rw = Cell.Row
      Txt = Format(Cells(Rw, "A").Value, "0.00")
      Cells(Rw, "C").Value = Txt & Cells(Rw, "B").Value
      Cells(Rw, "C").Characters(1, Len(Txt)).Font.Size = AssumedNumberSize
      Cells(Rw, "C").Characters(Len(Txt) + 1, Len(Cells(Rw, "B").Value)).Font.Size = AssumedTextSize
    Next
    Application.EnableEvents = True
  End If
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[jasonb75]

Given the nature of the worksheet change event, the OP might also need a little direction on how to make that work with any data that has already been entered into the sheet.