Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Cell Formatting to activecell with VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Cell Formatting to activecell with VBA
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 CopyPaste SpecialFormats. -- --Thomas [PBD] Working hard to make working easy. "upendra" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Activecell range copy | Excel Programming | |||
Copy File from activecell | Excel Discussion (Misc queries) | |||
Copy content of ActiveCell in a variable | Excel Programming | |||
Copy format of row above my activecell | Excel Programming | |||
ActiveCell.Copy Range("R3C27") | New Users to Excel |