JXLS 2.0 Conditional Formatting Forumla not updated per row

Question

Trying to create an JXLS excel template where it should be possible to copy conditional formatting from a cell on a specific row to the next generated row.

In the template, I create my formatting. If the value in the cell is equal to "yes" the row should be red.

Template

Conditional formatting

Formula: =$B2="yes"
Applies to: $A$2:$B$2

I know this formula works on an already populated excel sheet here is an example https://trumpexcel.com/highlight-rows-based-on-cell-value/

But when I do this with my excel template and JXLS 2.0 it fails. It copies the formula as it is to each new generated row. So instead of one condition for the whole sheet, there will now be as many as there are rows. The problem here is that it will copy it as is, which means that the formula in each condition will be based on the value in cell C2. So even if cell C3 is generated with the value "no" it will be red, since it is based on the value in C2.

Output excel

Condition Formatting output excel

Any tips on how to solve this directly in the template?

Using jxls 2.9.0 jxls-poi 2.9.0

Answer 1

One approach is to modify the formula in the template to acheive what we want.

Formula: =INDIRECT("$B" & ROW())="yes"

Description:

1. ROW() returns the current row number.
2. "$B" & ROW() gives the cell reference. For example, at row 5, we will get B5
3. Finally, using INDIRECT(...) we get the value at cell reference and check if is "yes".

---

Output excel:

Answer 2

What you are experiencing is standard Excel behaviour. In order to achieve what you want you have 2 options: using a regular Range or a dynamic table. I would use the latter.

Using a regular Range

You need to start with at least 2 rows like this:

and then only insert rows after the first row and before the last row. Never before first or after last. The new rows are picking up the same formatting because the underlying range is expanding. For example, inserting 4 rows in between results in:

Using a dynamic table

Assuming you have headers (you don't need to), you select your start range and then format it as a table:

You will have the option to choose if the table has headers or not via a checkbox in the dialog that will appear.

Then you add the same conditional formatting:

The difference now is that when you add a new row, the conditional formatting will automatically expand. The table itself automatically expands so everyting else (formatting, validation, formulas etc.) are expanding with it.

Just make sure you have the auto expanding option on for tables under File/Options/Proofing/AutoCorrect Options/AutoFormat As You Type/Include new rows and columns in table. You can do that programatically as well (I know in VBA you need to set Application.AutoCorrect.AutoExpandListRange to True). The default is True by the way.

No matter how big your table will get, you will have the formatting expanded.