• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Formating Problem, not updating

vijay.vizzu

vijay.vizzu

Member
Dear All,


I have just selected the region as A11 to AT 65000 and put a formula =$BB11<>$BB12 in conditonal formatting to format border lines. It works fine, but i inserted a row in between the some data, then it can't work for that. again i need to delete the formula in conditional format and put it again. Why this is happening i can't able to understand. Is there any VBA code to do it by pressing a button like deleting the previous format and again put it as same. like refreshing the format.


Thanks in advance
 
have you checked your conditional formating under "applies to" field, sometimes adding a row can change the "applies to" and has be adjusted.
 
Yes, changing the cell address like i am putting =$BB11<>$BB12 in formula bar, but after inserting a row it will changed like =$BB11<>$$BB14, so how can i keep it in stable?
 
the best practice that i have come accross is to select the first cell "leading cell" that will be affected by the conditional format and put in the formula. once it is set, I apply the range that will be affected in the "applies to" field. after that, i don't have issues. I can add and subtract rows and it doesn't affect, but as soon as i change the initial reference of that first cell by trying to "fix" the conditional formatting, then it will change everytime. just my 2 cents. so in summary, always conditionally format in the "leading cell" if you are going to affect a range.
 
Hi, vijay.vizzu!


The problem is that when you insert a new line let's say between line 15 and 16, the CF formula for line 15 passes from =$BB15<>$BB16 to =$BB15<>$BB17. As it should be, even if it's not what you want to do.


A workaround (which I'm not sure if I'd use it for 65K lines, test it by yourself and decide) is changing the CF formula to this:

=$BB11<>DESREF($BB11;1;0) -----> in english: =$BB11<>OFFSET($BB11,1,0)


So it will be checking each B cell against next row, no matter adds and deletes. It's a volatile function so it's calculated every time a cell changes. But it's your solution, the only one I can imagine for now.


Hope it helps.


Regards!
 
To improve SIRJB7's answer. Use Index, which is not volatile.


=$BB11<>index($BB11,1,0)


adjust accordingly. We are not sure what your data looks like exactly so its hard to specify the above formula correctly.
 
@Montrey

Am I missing something? I couldn't get it working, I don't get any CF applied. Only with this:

=$BB11<>index($BB11:$B65000,2,0)

Neither with $BB11:$BB65000,1.

Regards!
 
Dear SirJB7

Thank you so much for your idea. It works fine, but i didn't try montrey idea i.e =$BB11<>index($BB11,1,0). But i need one information from you all excel ninjas that Is offset and index will work same? as per my idea offset used for locating cells and index i don't have so much idea.... can u explain offset with comparision with index, how they differ from each other


Thanks in advance
 
Hi, vijay.vizzu!


INDEX has 2 formats:

a) matrix

INDEX(MATRIX, ROW, COLUMN)

b) reference

INDEX(REFERENCE, ROW, COLUMN)


It has additional parameters that you can check in the built-in help.


Basically it retrieves a value from a matrix provided row & column, or a cell based on a cell reference and row and column values.


It's not a volatile function, that's to say, it's only calculated when a dependent cell changes.


OFFSET has only one format.

OFFSET(REFERENCE, ROW, COLUMN, ROWSRANGE, COLUMNSRANGE)


It returns a cell or a range (if specified last two params as number of rows and columns) reference from an specified starting cell with displacement of rows and columns as 2nd/3rd params.


It's a volatile function, that's to say, it's always calculated when any cell changes.


Hope it helps. For further information, check built in help.


Regards!
 
Good explanation SirJB7!


Index can return whole arrays too by using this format.

=index((array1,array2)),,,1)


You could set the 1 to be linked to a cell with a drop down which would automatically change the arrays you want to view. This is the BEST for charting dynamically!
 
@Montrey

Hi!

Thanks. The idea was giving a general idea of what are each function intended to be used for: Index for searching within a matrix and Offset for retrieving a row&column relative displacement from a based cell. Not even using all the parameters, just the basics. Despite of that, the imagination of each person might do astonishing things which each function.

Regards!
 
You must log in or register to reply here.
Back
Top