Is there a way to copy the basic cell format from one cell to another without
copying the conditional format with it?

Just remove them after the paste:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/4/2009 by James Ravenswood
'

'
Range("A1").Select
Selection.Copy
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteFormats
Selection.FormatConditions.Delete
End Sub

--
Gary''s Student - gsnu200856


"joeeng" wrote:

> Is there a way to copy the basic cell format from one cell to another without
> copying the conditional format with it?

Thanks for your response. However, I didn't ask that question quite right.
What I really was trying to ask was, is there a way of copying a basic cell
format to another cell without overwriting the conditional format in the
destination cell?

"Gary''s Student" wrote:

> Just remove them after the paste:
>
> Sub Macro1()
> '
> ' Macro1 Macro
> ' Macro recorded 6/4/2009 by James Ravenswood
> '
>
> '
> Range("A1").Select
> Selection.Copy
> Range("A2").Select
> Selection.PasteSpecial Paste:=xlPasteFormats
> Selection.FormatConditions.Delete
> End Sub
>
> --
> Gary''s Student - gsnu200856
>
>
> "joeeng" wrote:
>
> > Is there a way to copy the basic cell format from one cell to another without
> > copying the conditional format with it?

If you know what basic cell formats you want you can just apply them:

dim fCell as range
dim tCell as range

set fcell = worksheets("Sheet1").range("a1")
set tcell = worksheets("sheet99").range("x99")

with tcell
.numberformat = fcell.numberformat
'add as many formatting characteristics as you want.
end with

joeeng wrote:
>
> Thanks for your response. However, I didn't ask that question quite right.
> What I really was trying to ask was, is there a way of copying a basic cell
> format to another cell without overwriting the conditional format in the
> destination cell?
>
> "Gary''s Student" wrote:
>
> > Just remove them after the paste:
> >
> > Sub Macro1()
> > '
> > ' Macro1 Macro
> > ' Macro recorded 6/4/2009 by James Ravenswood
> > '
> >
> > '
> > Range("A1").Select
> > Selection.Copy
> > Range("A2").Select
> > Selection.PasteSpecial Paste:=xlPasteFormats
> > Selection.FormatConditions.Delete
> > End Sub
> >
> > --
> > Gary''s Student - gsnu200856
> >
> >
> > "joeeng" wrote:
> >
> > > Is there a way to copy the basic cell format from one cell to another without
> > > copying the conditional format with it?

--

Dave Peterson

Understand, but what if one is trying to copy a range of cells with varying
formats (interior colors). Can it be done without using a slow for-next loop?

I tried

worksheets("Sheet1").range("A1:B99").interior.colorindex=worksheets("sheet99").range("A1:B99").interior.colorindex

but this apparently does not work.

"Dave Peterson" wrote:

> If you know what basic cell formats you want you can just apply them:
>
> dim fCell as range
> dim tCell as range
>
> set fcell = worksheets("Sheet1").range("a1")
> set tcell = worksheets("sheet99").range("x99")
>
> with tcell
> .numberformat = fcell.numberformat
> 'add as many formatting characteristics as you want.
> end with
>
> joeeng wrote:
> >
> > Thanks for your response. However, I didn't ask that question quite right.
> > What I really was trying to ask was, is there a way of copying a basic cell
> > format to another cell without overwriting the conditional format in the
> > destination cell?
> >
> > "Gary''s Student" wrote:
> >
> > > Just remove them after the paste:
> > >
> > > Sub Macro1()
> > > '
> > > ' Macro1 Macro
> > > ' Macro recorded 6/4/2009 by James Ravenswood
> > > '
> > >
> > > '
> > > Range("A1").Select
> > > Selection.Copy
> > > Range("A2").Select
> > > Selection.PasteSpecial Paste:=xlPasteFormats
> > > Selection.FormatConditions.Delete
> > > End Sub
> > >
> > > --
> > > Gary''s Student - gsnu200856
> > >
> > >
> > > "joeeng" wrote:
> > >
> > > > Is there a way to copy the basic cell format from one cell to another without
> > > > copying the conditional format with it?
>
> --
>
> Dave Peterson
>

If the colors vary between cells, you'll need to loop. If you know your
workbook, maybe you can group areas by color????

And if the colors vary between characters in the cell, then you'll be looping
through each cell and then looping between characters within each cell. (It can
be very slow if you have lots of colors/formats to preserve.)

joeeng wrote:
>
> Understand, but what if one is trying to copy a range of cells with varying
> formats (interior colors). Can it be done without using a slow for-next loop?
>
> I tried
>
> worksheets("Sheet1").range("A1:B99").interior.colorindex=worksheets("sheet99").range("A1:B99").interior.colorindex
>
> but this apparently does not work.
>
> "Dave Peterson" wrote:
>
> > If you know what basic cell formats you want you can just apply them:
> >
> > dim fCell as range
> > dim tCell as range
> >
> > set fcell = worksheets("Sheet1").range("a1")
> > set tcell = worksheets("sheet99").range("x99")
> >
> > with tcell
> > .numberformat = fcell.numberformat
> > 'add as many formatting characteristics as you want.
> > end with
> >
> > joeeng wrote:
> > >
> > > Thanks for your response. However, I didn't ask that question quite right.
> > > What I really was trying to ask was, is there a way of copying a basic cell
> > > format to another cell without overwriting the conditional format in the
> > > destination cell?
> > >
> > > "Gary''s Student" wrote:
> > >
> > > > Just remove them after the paste:
> > > >
> > > > Sub Macro1()
> > > > '
> > > > ' Macro1 Macro
> > > > ' Macro recorded 6/4/2009 by James Ravenswood
> > > > '
> > > >
> > > > '
> > > > Range("A1").Select
> > > > Selection.Copy
> > > > Range("A2").Select
> > > > Selection.PasteSpecial Paste:=xlPasteFormats
> > > > Selection.FormatConditions.Delete
> > > > End Sub
> > > >
> > > > --
> > > > Gary''s Student - gsnu200856
> > > >
> > > >
> > > > "joeeng" wrote:
> > > >
> > > > > Is there a way to copy the basic cell format from one cell to another without
> > > > > copying the conditional format with it?
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson