Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
REPOST: Need help with XL2007 FormatConditions, please?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
REPOST: Need help with XL2007 FormatConditions, please?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
REPOST: Need help with XL2007 FormatConditions, please?
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 - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
REPOST: Need help with XL2007 FormatConditions, please?
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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding FormatConditions in between other conditions | Excel Programming | |||
What is the parameter for FormatConditions? | Excel Programming | |||
FormatConditions VBA Bug? | Excel Programming | |||
FormatConditions(1).Formula1 | Excel Programming | |||
font size on formatconditions | Excel Programming |