Conditional formatting with ISFORMULA stopped working.

Good morning,

Since a few days I have an issue with a conditional formatting rule which I have been using for years.
I have searched the forum but could not find a logical solution to the problem.
I presume the issue occurs either due to some Microsoft update or due to a box that has been (un)ticked somewhere since no changes were made to the conditional formatting rules since 2019 :-).

The issue:
- Whenever I have a new cell with a formula or whenever I c/p a formula as value the conditional formatting with =ISFORMULA doesn't update.
- Other conditional formatting rules do update.

I have added a testfile.
I use Office365

Desired resolution:
- I want it to work again obviously ;-)
Whenever I add a line to the table or update a cells content all conditional formatting rules should work as they did.

When does it work?
- When I open the file all formatting is updated.
- When I open the conditional formatting dialogue and update any line

When doesn't it work?
- When adding a new line to the table
- When i would put a formula in column B or C outside of the table.
- When pressing F9

Used rules:
Formula: =ISFORMULA(B1)
Applies to column B: =$B:$B
Makes the text blue

The same rule is set for the third column.
Formula: =ISFORMULA(C1)
Applies to column C: =$C:$C
Makes the cell green

Other rules:
- In the testfile I added some formatting to color some cells when the are higher than 1, 2,5 and 3.
This does work every time something is added

One of the things I checked is if EnableFormatConditionsCalculation = True and it is.

- Does anyone know the solution?

Hello Richard, thank you for your reply.

My bad. I see the CF range changed because I removed some cells in the testfile before I uploaded it.

Basically what I want to see is that all cells with formulas show a green background in column C and a blue text in column B.
I don't think the CF itself is wrong, since it has already worked for years, but since 2 days it stopped working.
Even when I open older versions of this file or try it in newer files, the ISFORMULA CF just doesn't update.

Cell by Cell:
Step 1:
New input of "random text" in cell A11
This triggers the table to expand to include line 11 and automatically places formulas in B11-G11
B11 = formula: B11 shows the same time as C10. <and extra: it shows a blue text via CF>
C11 = formula: C11 shows the current time <and extra: it shows a green background via CF>
D11-G11 = formula: shows other data.

Step 2:
I finished my task and place a hardcopy timestamp in C11.
I then proceed to put in a new text in A12 and so on and on.

After the hardcopy timestamp normally the CF would right away see C11 is no formula and show a regular background again plus the new line, line 12, should show CF for the formulas in B12 and C12, but Excel just doesn't update any CF with with ISFORMULA in it.

p.s. I added the CF in column I to check if non-formula CF's work and they do.
p.p.s. Thanks. I know about the whole column reference thingy. This file is very small so I didn't really bother to adjust the range (-:

Hi,
When you do CF based on a formula and you select a full column, then you always have to refer to the first row. The CF will then check for every row if the formula applies.

I tried to remove and add the CF's and even used a whole new sheet. Both didn't work.
Then I did it with a range that didn't contain a table: that did work.

I ended up only selecting the table data and applying the rule to =$B$2:$B$2957 instead of =$B:$B and that did the trick. (And when adding a new row to the table the CF expands to that row).

Apparently Microsoft last week changed something in the behavior of the conditional formatting as I found some other new posts this weekend where people suddenly had failing CF's since last week.

I'm happy it's resolved and thanks for your time and suggestions!