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.