Formatting Linked Cells

[Guest]

Forsay I have the following text in one cell:

Bold text (such as a word) - normal text (such as the defenition)

I want to use this cell in a formula, so when it is updated on it's sheet,
the sheet that it's link to changes with it, but keeps that formatting.

At this time, when I call it (or link it) Excel removes all formatting. I
don't want to go into each cell and format it myself each time. There are
too many tabs/sheets.

Any suggestions?

 

[JE McGimpsey]

Formulae can only return values to their calling cells, they can't set
formatting. To do what you want would require replacing the formulae
with Event macro(s).

An example might be (put in the worksheet code module):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rDest As Range
Dim i As Long
Dim bFound As Boolean
bFound = True
With Target
Select Case .Address(False, False)
Case "A1"
Set rDest = Sheets("Sheet2").Range("J4")
Case "B3"
Set rDest = Sheets("Sheet3").Range("L17")
Case Else
bFound = False
End Select
If bFound Then
rDest.Value = .Text
For i = 1 To Len(.Text)
rDest.Characters(i, 1).Font.Bold = _
.Characters(i, 1).Font.Bold
Next i
End If
End With
End Sub

or, since changing format doesn't fire the worksheet change event, you
may want to use the worksheet_calculate, worksheet_Activate, or
workbook_SheetActivate events instead.


Billy Bob Danny Frank <Billy Bob Danny