Just for the record, I used FillDown. ClearContents took out
formulas, too, so I had to add a Select Case to filter out those cells
that didn't get cleared. After running this, the Confitioinal
Formatting ranges were simply extened, not copied, so I am not
multiplying formats like rabbits! This was all triggered by adding a
new date into the worksheet, so here's what I came up with.

Ed


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 4 Then

Dim wks As Worksheet
Dim rng As Range
Dim x As Long, lst As Long
Dim dt As Date

Set wks = ActiveSheet
lst = wks.Range("A10000").End(xlUp).Row

For x = 1 To 19
Set rng = wks.Range(Cells(lst, x), Cells(lst + 1, x))

Select Case x
Case 2, 7, 8, 12, 13, 14
rng.FillDown
Case 4
dt = rng.Cells(2, 1)
rng.FillDown
rng.Cells(2, 1) = dt
Case Else
rng.FillDown
rng.Cells(2, 1).Value = ""
End Select

Next x

For x = 55 To 74
Set rng = wks.Range(Cells(lst, x), Cells(lst + 1, x))
rng.FillDown

Select Case x
Case 68, 69, 71, 72, 73, 74
'
Case Else
rng.Cells(2, 1).Value = ""
End Select

Next x

wks.Range("A" & lst + 1).Select

End If

End Sub




On Aug 12, 3:33*pm, Ed from AZ wrote:
Hi, Barb. *Yes, I'm sure once I get this thing cleaned up, that would
indeed be a much better way to handle things.

Right now, it's the clean up process that has me stumped. *Right now I
need to clear out all these extra formattings and extend the ranges of
the good ones to the bottom of the used range. *I guess I could simply
go in manually, take screen shots of all the colors and formats and
such, and rebuild each one by hand. *I would much rather simply extend
each range. *Or, failing that, access the conditions of a known good
format, add another with those conditions that runs the whole range,
and then delete the first.

Bottom line, though, is that there seems to be a real lack of info on
the new XL2007 FormatConditions object and how to access it. *Excel
Help is even worse than useless at this point. *I really wish there
was some good info for this.

Ed

On Aug 12, 8:34*am, Barb Reinhardt



wrote:
I'm sure there's a more elegant way, but couldn't you do something like this

dim myRange as range * *

Set myRange = Range("A2") *'Range to copy

myRange.offset(1,0) = myrange
myrange.offset(1,0).clearcontents
--
HTH,
Barb Reinhardt

"Ed from AZ" wrote:
In an XL2007 .xlsm workbook, I have a Worksheet_Change macro that
captures the row I'm entering data into and copies down all the
formats and formulas from the row above. *This is done by selecting
certain ranges and cells, using Copy, and then PasteSpecial.

Apparently this is screwing up my conditional formats. *Thanks to Jim
Rech, I dug a bit deeper into FormatConditions. Now I am totally
confused!

I have a dozen columns with conditional formats. *Yet when I did
* * Set wks = ActiveSheet
* * MsgBox wks.Range("A21:Z1000").FormatConditions.Count
I get 1188 FormatCondition objects!!??

Really, all I want to do is iterate through each column and
ModifyAppliesToRange to extend the format one cell farther down. *Am
I
going to have to ClearFormats and Add the format back in with the new
range every time?

Anyone who can help me make sense of this is most appreciated!!

Ed- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -