Copy cell formatting thru formula

[sharan]

Hi!!

My requirement is as follows

i have 2 sheets in my workbook

1. sheet1 (user input)
2. sheet2 (write protected, only for viewing data)

Range "a8" in "sheet1" and "a8" in "sheet2" are related

when user changes the formatting of Range "a8" in "sheet1",

i want it to be reflected in "sheet2"

i tried it in the following way, but did not work

a. made a macro(copy_data) that will copy the contents with formatting

b. i had set the formula for range "a8" in sheet2 as "=copy_data(sheet1!a8)"


this method didnt work out
Please help

 

[k209310]

try something like =Sheet1!B13

an easy way to do this is select the cell you want the data to be copied to in the forula bar press = then go to the cell you wich the data to be copied from when the cell is selected press the Tick.

Hope this helps

 

[Barry Katcher]

You can use this macro:

Sub CopyPaste()
Sheets("Sheet1").Select
Range("A8").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A8").Select
ActiveSheet.Paste
End Sub

 

[sharan]

This didnt work out, as it copies only the contents and not formats.

 

[sharan]

Hi Barry!!

this did work, but not as expected, i made the following function


Public Function copy_cont(a_range As Range) As String

a_range.Select
Selection.Copy
Set a_range = a_range.Next
a_range.Select
ActiveSheet.Paste

End Function


i used the following formula for a cell

=copy_cont(A1)

but nothing happened

i made a command button and assigned the code as



Private Sub CommandButton1_Click()

copy_cont Range("a1")

End Sub


when i click the command button it works,

but i wanted it to work in a formula

Expecting a suggestion

 

[Tom Urtis]

You are running into 2 difficulties:

(1)
Formulas do not change formatting. A calculation event can capture changes in formulas but see below for a more efficient approach.

(2)
Formatting a cell will not trigger an event.

To achieve the effect of seeing cell A8 in Sheet2 look just like cell A8 in Sheet1, the following should suffice in most cases.

Right click on the Sheet2 tab, left click on View Code, and paste this into the large white area that is the Sheet2 module:

''''''''''''''''''''''''''''''''''''''''''''

Private Sub Worksheet_Activate()
ActiveSheet.Unprotect ("YourPassword")
Worksheets("Sheet1").Range("A8").Copy Range("A8")
ActiveSheet.Protect ("YourPassword")
End Sub

''''''''''''''''''''''''''''''''''''''''''''

Modify for password. Return to the worksheets by pressing Alt+Q.

 

[sharan]

Thanks!! TOM

It was amazing, it worked out..

I thank all Mr. Excel.com members for giving a thougt to my problem.