Copy formats from an indirect cell

[PiggyChu620]

I managed to copy cell formats <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">within the same sheet</code> by using

[COLOR=#101094][FONT=Consolas][FONT=inherit]Dim[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit] c [/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]As[/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]Boolean[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]

[/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]Private[/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]Sub[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit] Worksheet_Change[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]([/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]ByVal[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit] Target [/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]As[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit] Range[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit])[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]
[/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]If[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit] c [/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]Then[/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]Exit[/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]Sub[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]
c [/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]=[/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]True[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]
f [/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]=[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit] Mid[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]([/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]Target[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit].[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]Formula[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit],[/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]2[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit])[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]
Range[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]([/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]f[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]).[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]Copy
Target[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit].[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]PasteSpecial xlPasteAllUsingSourceTheme
c [/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]=[/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]False[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]
[/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]End[/FONT][/FONT][/COLOR][COLOR=#101094][FONT=Consolas][FONT=inherit]Sub
[/FONT][/FONT][/COLOR]

But in <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">another sheet</code>, I use <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">=INDEX(Sheet1!$I$1:$J$255,MATCH(A4,Sheet1!$F$1:$F$255,0),1)</code> to get the value from <code style="margin: 0px; padding: 1px 5px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; vertical-align: baseline; box-sizing: inherit; background-color: rgb(239, 240, 241); white-space: pre-wrap;">Sheet1</code>.

Now how can I copy the source format just like the cells in the same sheet would!?

I had tried everything in Google Search to no avail!

I even tried to make my own function and still failing to do so!!!

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">[COLOR=#858C93][FONT=inherit]'(One variation)[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Function[/FONT][/COLOR][COLOR=#303336][FONT=inherit] CopyFrom[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#101094][FONT=inherit]ByVal[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Cell[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] r [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Range

    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]Set[/FONT][/COLOR][COLOR=#303336][FONT=inherit] r [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Worksheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]Cell[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Parent[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Name[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]Cell[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]address[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]External[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]False[/FONT][/COLOR][COLOR=#303336][FONT=inherit]))[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    CopyFrom [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] r[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Value2
    r[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Copy
    Application[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Caller[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]PasteSpecial xlPasteAllUsingSourceTheme

[/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Function
[/FONT][/COLOR]</code>

I'm kind of at the end of my rope now!

Somebody PLEASE be so kind and teach me how to do it!

Much appreciated!!!

 

[EFANYoutube]

Your close
Try this

Sub Formats()
    Sheets("Sheet1").Range("A1").Copy
    Sheets("Sheet2").Range("D2").PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
End Sub

 

[PiggyChu620]

Your close
Try this

Sub Formats()
    Sheets("Sheet1").Range("A1").Copy
    Sheets("Sheet2").Range("D2").PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
End Sub

This worked if it's a "direct reference". Where you know the source and target cell as "A1" or "D7". But if my reference is "indirect", such as my example above, it failed! Unless I could somehow get the direct link from my formulas! I'll see what I can do. Thanks!

 

[EFANYoutube]

This worked if it's a "direct reference". Where you know the source and target cell as "A1" or "D7". But if my reference is "indirect", such as my example above, it failed! Unless I could somehow get the direct link from my formulas! I'll see what I can do. Thanks!

Oh yeah sorry. You need to preform a find() on a range. Looks like you only use 1 match so your out put goes into to a list not a matrix so…

 Function pasteFormats(searchVal as variant, toRng as range)

Dim fromRng as range
Set fromRng = thisworkbook.sheets("Sheet1" ).Range("F1:F255")

fromRng.find(searchVal).Copy 'or if your copy Val is in G then 
'fromRng.find(searchVal). Offset(0, 1).Copy

toRng.PasteSpecial Paste:=xlPasteFormats

Application.CutCopyMode = False

End function