Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format Conditions Formula1
Good Evening,
I created a VB macro in Excel 2003 that worked great with conditional formatting but now I am having trouble in Excel 2007. I have a large spreadsheet I use to take a data dump of master data out of another system. In this excel file it highlights those cells that are not in compliance so that I know which ones need to be fixed. Excel highlights these perfectly using conditional format. I then created a VB Macro that would evaluate each row and if there was no items in that row with an active conditional format (indicating an error) it would hide the row for me so that when the macro is complete it will only be displaying rows where I have an issue. Now I am in the process of testing the spreadsheet for Excel 2007 and have run into a major problem. Throughout my document I use similar formula for many cells (one example '=LOOKUP($I13,$I$4:$I$7)< $I13'. In 2007 this formula is "applied to cells" '=$I$13:$I $25000'. In my code I have a snippit such as: For Ndx = 1 To Rng.FormatConditions.Count Set FC = Rng.FormatConditions(Ndx) Application.Evaluate(FC.Formula1) Example if I was evaluating something in row 25: In 2003, FC.Formula1 would return '=LOOKUP($I25,$I$4:$I$7)<$I25' In 2007, FC.Formual1 returns '=LOOKUP($I13,$I$4:$I$7)<$I13' Notice that it is not updating my reference to be row 25 but instead always evaluating based on the initial formula. Is there a call that I can use to return the active formatcondition reference so that I can continue to do an evaluate statement? If not, does anyone have a recommendation on how to only show rows with an active conditional format. I know in 2007 I can display based on colors but I have 20 colums that have different conditions so that would be very hard to try and navigate. Thanks so much for your assistance! Tim |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula1-ferrari collection | Excel Worksheet Functions | |||
formula1-ferrari collection | Excel Discussion (Misc queries) | |||
formula1-ferrari collection | Excel Discussion (Misc queries) | |||
FormatConditions(1).Formula1 | Excel Programming | |||
Passing String to Formula1 Method in VB | Excel Programming |