Reference Cell Formatting

[chelseasikoebs]

Is it possible to carry over the formatting of the text from the source cell to the destination cell that contains a formula? When I do a copy/paste formatting only, it doesn't paste the bold and color formatting of the text. It only pastes the size of the text.

For example:
TAB "INPUT", CELL D20:
Supplies for shop use *credit for tax will be on the next statement*

TAB "LOGS", CELL E77:
=IF(Input!$D$20="","",Input!$D$20)

Then if cell E77 does return the value of cell D20, I want it to have the same bold, red formatting on part of the text D20 has. But, if I can't paste formatting only and get the bold & red to paste, then I can't run a macro.

??

 

[mikerickson]

No, unfortunatly.
A cell that has a formula cannot have different portions formatted differently.

 

[chelseasikoebs]

Not even with VBA?

 

[TwistShout]

quite easily with vba, i would imagine.

just record a macro of changing the color and bolding the text. Here's an example (you just need to change what's selected at beginning. that color is yellow, change number for different color.)
Range("B5").Select
With Selection
.font.bold = true
.Interior.Color = 65535
End With

 

[mikerickson]

With VBA one could link two cells so that they have similar mixed formatting.
But, if either cell contained a formula (even a UDF) the mixed formatting would not work.

The issue revolves around the .Characters property of a Range.

If A1 contains =D5 and D5 has the mixed text ABCXYZ


Range("D5").Characters(1,3).Text will be "ABC" and
Range("D5").Characters(1,3).Font.Underline = True

But

Range("A1").Characters(1,3).Text will be "=D5", not "ABC"

If you want to replace the VLOOKUP with a ChangeEvent routine, that both looks up and applies mixed formatting, that would be possible, but any change to that VLOOKUP would require VBA skills on the part of the person who maintains the worksheet.

Another option would be to have two columns, one to hold the "Supplies for shop use", the other to hold "*credit for tax will be on the next statement*" each of which has its own full cell formatting.