Capture Conditional Format Item

  • A quickie...

    Anyone know of a way to capture the Activecell's conditional format properties without actually evaluating the condition in your code ?

    For example... I have a range of cells with two conditions as follows:

    Condition1 if the following is true =OR((P17/O17)*100<$FD$4,P17<$FE$4) then cell interior colorindex = 35

    Condition2 if the following is true =OR((P17/O17)*100<$FD$5,P17<$FE$5) then cell interior colorindex = 45

    else just leave the formats alone...

    Let's say I select a cell with "Green" interior as a result of the conditional formattin & type the following in the immediate window

    ? ActiveCell.Interior.Colorindex

    I get the answer 2 (i.e. white background) - where I would expect 35 - which suggests that you cannot pick up the volatile Interior.ColorIndex property set by Conditional ormatting in your code...

    So I seem to be faced with testing the formatconditions in order to know which format the cell is taking... unless anyone else knows how to capture it...

  • Hi Will,

    You can access properties of a particular condition using

    ? ActiveCell.FormatConditions.Item(1).Interior.ColorIndex

    But the question that WHICH item is currently activated is still not known...

    Looks to be an interesting problem, but I think this can be a good starting point

    Thanks: ~Yogendra

  • Hi mate,

    How's married life treating ya? :yikes:

    OK... trouble is.. i want ti ID the format currently operating on the cell... i.e. is it format1, format2 or neither.... (as i want to do stuff with the cells dependant on whether they are formatted in a particular way...)

    so your code gives this

    ? ActiveCell.FormatConditions.Item(1).Interior.ColorIndex
    ? ActiveCell.FormatConditions.Item(2).Interior.ColorIndex

    in y example... but that is info I already know... I really need to know which format is operating on the activecell.... and I'm getting to think that I cannot find that out unless I evaluate the format criteria for each cell...

    Hope this makes sense..

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!