Forum Discussion
Cannot edit conditional formatting formula.
I am unable to edit a conditional formula. If I have a formula =OR(B1>C1,C1>D1,D1>E1) and I want to change the formula to be =OR(B1>=C1,C1>=D1,D1>=E1) I cannot put the cursor after the ">" nor I...
Select the range to which you want to apply the rule.
The first (top left) cell in that range should be the active cell in the selection.
Note the address of that cell in the name box on the left hand side of the formula bar.
On the Home tab of the ribbon, select Conditional Formatting > Manage Rules...
Select the rule, then click Edit Rule...
Click in the formula and press F2. You should then be able to use the arrow keys, as well as the Home and End keys, to move the insertion point.
Make sure that the formula is correct with respect to the active cell. For example, if the range starts in row 1, the formula should refer to B1, C1 etc.
But if the range starts in row 4, the formula should refer to B4, C4 etc.
- Afraid you are not understanding the OP. This is a real software bug. I have the same thing happening with conditional formatting using formulas. On a sheet of even moderate complexity, with conditional formatting formulas applied to whole column ranges, the formula edit text field will at some point not accept mouse clicks any more. This means you cannot position the cursor into formulas to make adjustments.
If you close and reopen excel, you can get ONE mouse click to edit ONE formula. Next time you open conditional formatting, you again can't click in the field and must save and restart excel again to be able to edit exactly one more.
This is especially difficult since despite ANY logic saying it's a good thing to do, the left and right arrows are hijacked at all times regardless of UI focus, so you cannot cursor in to edit parts of the formula with a keyboard. (A bad design choice i won't get into here.)
Bottom line: This is not the users fault, it's a real bug that bot past Microsoft QA.
For anyone suffering this bug: CTRL-S to save, then close excel, then reopen the workseet. You'll probably be able to click the mouse one time to make a conditional formatting formula edit before the bug arrives. Rinse and repeat until your sanity wears out.You seem to have misunderstood HansVogelaar.
He didn't say you would be able to edit the formula with a mouse, he said that you can override the hijacking of the left and right arrows by pressing F2 whilst in the edit box. This allows you to move the cursor with the left and right arrows.
- Thank you for the explanation. This BUG is really annoying
- PS : It seems the same is happening when editing data validation messages.
Also after a cold start of my PC I can edit quite OK for a while.
Maybe these input will assist Microsoft in getting the the root cause.
It surely seems as if the cursor loses its focus over time. Might even be Windows and not Excel specific.
