Copy Cell Formatting to activecell with VBA

[upendra]

Hi,
I wrote the below code to copy formatting of the selected cell to
current cell. But its not working. Kindly let me know whats the fault
with the code

Public Function CopyFormat(myrng As Range)
If myrng.Interior.ColorIndex = xlNone Then
Exit Function
Else
ActiveCell.Interior.ColorIndex = myrng.Interior.ColorIndex
ActiveCell.Interior.Pattern = myrng.Interior.Pattern
End Function


Regards,
Upendra

 

[Thomas [PBD]]

upendra,

First things first, you were missing the End If before the End Function.
Secondly, I believe with what you are attempting, you should probably run
this as a Subroutine. Like so:

Public Sub CopyFormat()

Dim myrng As String
myrng = InputBox("Enter Range")

If Range(myrng).Columns.Count = Selection.Columns.Count Then
If Range(myrng).Rows.Count = Selection.Rows.Count Then
Range(myrng).Copy
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Else
MsgBox "Ranges must be the same size", , "Error"
Exit Sub
End If
Else
MsgBox "Ranges must be the same size", , "Error"
Exit Sub
End If
End Sub

Basically, what I have amended it to do is require a pop-up box for the
input range, place something like B2 in the box. This will only do one cell
at a time, or multiple cells, however your range has to be the same size.

Just a note: This could probably be done just as quickly with Copy>Paste
Special>Formats.