Format Conditions Formula1

[timothy.huebsch]

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

 

[Jim Rech]

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.

 

[Tim]

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