Search code examples
excelexcel-2007conditional-formatting

Conditional formating not updating area of application in table(list object)

Well, I have found something weird and I'm kind of shocked right now.

I have several Excel Files and normally we use tables to store the data (tables I mean ListObjects) and we apply Conditional Formating rules, so when a new row is inserted into the table, the conditional formating rule expands instantly:

enter image description here

If I insert a new row in this table, my conditional formatting rule instantlly applies to new row.

enter image description here

As you can see in the second image, a new row has been inserted and it becomes red instantlt because our conditional formating rules. This works perfect.

Now I have another file with different information, but it's the same idea. Table, data and conditional formating.

enter image description here

Data is just a list of invoices (sorry, but I had to remove the Vat Numbers and Company names for privacy). Column C is a VLOOKUP formula, combined with an IFERROR. It searches the name of the company (company name is in column D) and if something is found, it returns the Vat Number. IF not, it returns value No encontrado. The formula works perfectly.

Now, I have created a conditional formating rule that, if value in column C is No encontrado it highlights in yellow all cells in row. This rule works perfect too. We have used it thousand of times.

Now comes the weird part. If I insert a new row and subtotals row is not active in the table, my conditional formating rule instantlt applies to new row. And that's perfect!

enter image description here

As you can see in the image above, the new row is yellow because the formula can't find anything because company cell is in blank. This is perfect and works good.

Now comes the weird part and I have no idea how to get a solution. If we insert a new row but subtotals row of table is active then, the conditional formating rule does not applies to new row!

enter image description here

As you can see in the image above, last row shows value No encontrado but the conditional formating rule is not applying. So I have to manually update area of application of conditional formating rule.

Let's go back to first file I showed in the first 2 images of this question. If you check the second image, subtotals row is active, but conditional formating rule updates area of application.

So my question is. Why in this second file, my rule of conditional formating is not updating area of application if I insert a new row with subtotals row active?

Things I know are not the problem:

  • Both files are xlsx, so no macros or VBA are affecting.
  • Both files have been created with same version of Excel and same PC.

Things I have tried:

  • Creating the file again starting from zero. It did not solve anything.

Thanks in advance. If you need more information, please, feel free to ask.

UPDATE: Well, I've done some research and discovered something. It looks like Data Validation Lists blocks Conditional Formating autoexpand. In Columnd D (company name), I've created a Data Validation Dropdown List. Users choose the company. This Data validation blocks Conditional Formating from updating area of application. If I disable the data validation in that column, then the CF rule works perfect, even with subtotals row active.

Any idea of how to deal with this bug? I know I can fix it with VBA but I'm trying to find a solution without VBA. I would like the file to be XLSX. Also, In this company they never update software, so solution must work for Excel 2007.

UPDATE #2: My CF rule just checks if in column C appears the value No encontrado. If found, it highlights in yellow all cells in that row of the table:

enter image description here

The formula I'm using is: =IF($C11="No encontrado";TRUE;FALSE)

This CF rule applies to all the table (right now is range =$A$11:$K$609

And the formula in column C is:

=IFERROR(VLOOKUP([PROVEEDOR];T_PROVEEDORES;2;FALSE);"No encontrado"). It searches for value in cell D in another table called T_PROVEEDORES. IF nothing is found, then it returns No encontrado.

In column D is just a Dropdown List with different company names.

Also, in Column D I got a Data Validation Dropdown List of companies. Thanks in advance.

UPDATE #3: I've uploaded a sample file to my GD. I deleted all privacy information and used generic names.

Download file

In the sample file, worksheet COMPRAS, there is a table. If you insert a new row using TAB key and subtotals row is not active, the new row will be highlighted in yellow, and that's how it should work.

But if you do the same with subtotals row active, then it does now work. CF rule does not update area of application.

Solution

  • Good news and bad news.

    Bad news is, this is actually an issue in Excel 2007 that was fixed in later versions.

    Good news is there is an answer, just not the cleanest. Make the range of your conditional formatting include the entire worksheet: Conditional Formatting workaround

    The problem is the conditional formatting range doesn't continue to expand with new rows. If the range is set in this fashion, it forces Excel to set your formatting as the range includes all possible rows. My example just shows the yellow row. Repeat what i have done for the other three conditions as well.