I have a woorkbook that has a list of movies, all in column A. In the rest of
the columns I use =A9 and so on. The info goes to the cells just fine but I
would also like it to take the color of the text! Is this possible?
Not without VBA.
You could use an event macro to copy the format, for example.
To enter this, right-click on the sheet tab; select View Code and paste the
code below into the window that opens.
Adjust Src and AOI to reflect your actual ranges for the columns and the
location of your formulas.
You'll need to edit it depending on the exact layout of your sheet, but this
should get you started.
Post back if any more problems.
Please note that merely changing the format does NOT trigger a Change event.
So you should probably format the cell in column A before entering the
information or before entering the =A9 formula in some other cell.
Also please note that as written, the code will only work for dependent cells
on the same worksheet.
=============================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aoi As Range
Dim Src As Range
Dim c As Range
Set Src = [A:A]
Set aoi = [H:H] 'set this to the range you wish to copy colors to
'Have to check for new entries in either Src or aoi
If Not Intersect(Target, Src) Is Nothing _
Or Not Intersect(Target, aoi) Is Nothing Then
Application.EnableEvents = False
Set Src = Src.SpecialCells(xlCellTypeConstants, xlNumbers + xlTextValues)
On Error GoTo Handler
For Each c In Src
c.Copy
c.Dependents.PasteSpecial (xlPasteFormats)
Next c
End If
GoTo Last
Handler:
If Err.Description = "No cells were found." Then Resume Next
MsgBox ("Error: " & Err.Number & " " & Err.Description)
Last: Application.CutCopyMode = False
Application.EnableEvents = True
End Sub
====================================
--ron
