I TELL A CELL =B1 BUT I ALSO WANT IT TO TAKE THE TEXT COLORS
 

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?

Hi Don't

This is not possible with a worksheet function

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Don't" wrote in message ...
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?

On Sun, 25 Sep 2005 06:13:03 -0700, "Don't"
wrote:

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

On Sun, 25 Sep 2005 11:04:16 -0400, Ron Rosenfeld
wrote:

On Sun, 25 Sep 2005 06:13:03 -0700, "Don't"
wrote:

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

Just a thought, if you want to format multiple columns with the same formatting
as in column A, but only the first column of that range has the =An formula in
it, you could modify the macro slightly to read:

========================
Dim c As Range

Set Src = [A:A]
Set aoi = [H:L] '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.Resize(1, aoi.Columns.Count).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