conditional format: looking for formated

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Candyman - 15 Feb 2007 00:19 GMT

I want to hide other rows that Do NOT have aconditional format.. We have a
VLOOKUP driving the conditional formating. It turns the cells RED. I want
to examine the cells in the rows and hide the rows that do not have the
conditionalformat changed to RED.

I tried but it did not pick uo the color index:
If Cells(x, i).Interior.ColorIndex = 3 then Cells(x,
i).entirerow.hidden=true

The cells.ColorIndex always come back as "-4142". If I format a cell to RED
then it will return a value of 3.

How do i pick the cells that were turned RED via conditional formatting?

OR do I have to mimic the VLOOKUP formula,(could get messy.

Thanks

Reply to this Message

Tom Ogilvy - 15 Feb 2007 03:32 GMT

The easiest is to check the same condition the conditional format is
checking. (which is what I assume you mean by mimic the vlookup).

Chip Pearson shows how to do this without knowing beforehand what that
condition is.
http://www.cpearson.com/excel/CFColors.htm

Signature

Regards,
Tom Ogilvy

>I want to hide other rows that Do NOT have aconditional format.. We have a
> VLOOKUP driving the conditional formating. It turns the cells RED. I
[quoted text clipped - 15 lines]
>
> Thanks

Reply to this Message

Candyman - 16 Feb 2007 00:05 GMT

A Lot of good stuff, but I can not get the results on the VLOOKUP conditional
formula.

There is only one condition.

I am using :

Set FC = Cells(x, i).FormatConditions(1)
Formula_1 = (FC.Formula1)
Result = Application.Evaluate(FC.Formula1)

The code works for some formaulas:
=ISNA(MATCH($A22,I:I,FALSE))

The code jams on the last line for a lookup statement that compares two
lists and highlights changed cells:
=D22 <> VLOOKUP($A22,PreviousReviews,COLUMN(D22),FALSE)

Any ideas?

> The easiest is to check the same condition the conditional format is
> checking. (which is what I assume you mean by mimic the vlookup).
[quoted text clipped - 22 lines]
> >
> > Thanks

Reply to this Message

Tom Ogilvy - 16 Feb 2007 02:35 GMT

I suspect the problem is that you are not using absolute references in your
formula, so it probably isn't what you think it is.

> Set FC = Cells(x, i).FormatConditions(1)
> Formula_1 = (FC.Formula1)

msgbox Formula_1

> Result = Application.Evaluate(FC.Formula1)

Signature

Regards,
Tom Ogilvy

>A Lot of good stuff, but I can not get the results on the VLOOKUP
>conditional
[quoted text clipped - 46 lines]
>> >
>> > Thanks

Reply to this Message