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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format Conditions Formula1
I've found in applying formula conditional formats to a range of cells in
Excel 2007 that the formula you see when you edit a rule is for the first cell, regardless of the cell in the range you pick. This definitely is a confusing change from Excel 2003. Nevertheless the conditional formatting works fine for all the cells. MS should have documented this change, and maybe they did, but I haven't found it. -- Jim wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Format Conditions Formula1
That is what I have found also. The conditional formats work great
but is there a way I can get at the executing formula from VB instead of the static initial formula? Any help is greatly appreciated! Thanks, Tim On Jun 8, 7:58*pm, "Jim Rech" wrote: I've found in applying formula conditional formats to a range of cells in Excel 2007 that the formula you see when you edit a rule is for the first cell, regardless of the cell in the range you pick. *This definitely is a confusing change from Excel 2003. *Nevertheless the conditional formatting works fine for all the cells. *MS should have documented this change, and maybe they did, but I haven't found it. -- wrote in message ... 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- Hide quoted text - - Show quoted text - |
Reply |
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 |