why don't you just copy the entire worksheet

Sheets("Sheet3").Copy After:=Sheets(Sheets.Count)

This will preserve to formating. You can then delete items from thne old
worksheet that needs to get entered each week.

"davegb" wrote:

I have a weekly status sheet I re-create each week. I'm writing code
to do it. So far, it creates the new sheet and copies the formatting
from the first 3 cells of the old sheet into the new one. They all use
conditional formatting. What I want to do next is open the conditional
formatting dialog and enter new colors for the font and background,
then close the dialog and have the macro continue and copy the colors
I manually selected to the other cells (B1 and C1), but leave the
conditions intact. Does anyone know how to do this?
Here is the code thus far:
Sub NewWklySht()
Dim wsNewWklySht As Worksheet
Dim wsCurWklySht As Worksheet

Dim NewShtName As String

Set wsCurWklySht = ActiveSheet
NewShtName = Format(CDate(ActiveSheet.Name) + 7, "mmm dd")
Worksheets.Add(befo=Sheets("Project Summary")).Name = NewShtName
Set wsNewWklySht = Worksheets(NewShtName)

wsCurWklySht.Range("A1:C1").Copy
wsNewWklySht.Range("A1:C1").PasteSpecial Paste:=xlFormats,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
wsCurWklySht.Range("C1").Copy
wsNewWklySht.Range("C1").PasteSpecial Paste:=xlFormulas,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub

Thanks in advance.