REPOST: Need help with XL2007 FormatConditions, please?

[Ed from AZ]

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

 

[Barb Reinhardt]

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

 

[Ed from AZ]

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

 

[Ed from AZ]

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