# Editing long conditional formatting formulae

#### markly

##### New Member
In Excel97, I have longer conditional formatting formulae that can be displayed in the formula window, and I need to edit them. But when I move the cursor to the end of the window to get the next bit of formula, the cursor shoots to the end of the formula, and I can't get the middle piece to be displayed for editing it.

The formulae are like this:
=AND(Z6=8,(COUNTIF(A6:CC6,8)+COUNTIF(Z2,8)+COUNTIF(Z4,8)+COUNTIF(Z8,8)+COUNTIF(Z10,8)+COUNTIF(Z12,8)+COUNTIF(Z14,8)+COUNTIF(Z16,8)+COUNTIF(Z18,8)+COUNTIF(H2,8)+COUNTIF(H4,8)+COUNTIF(Q2,8)+COUNTIF(Q4,8))=1)

That is, if the value of Z6 is '8', and it is the only 8 in row6 from cols A to CC, and the only 8 in (1) rows 2, 4, 8, 10, 12, 14, 16 and 18 of col Z, (2) rows 2 and 4 of col H, and (3) rows 2 and 4 of col Q, then format Z6.

Using the right arrow to move along the formula, even with Ctrl or Alt, doesn't work. It simply puts in unwanted cell references wherever the cursor line happens to be.

Expanding the window by clicking the symbol at the right does not give me the entire formula in two windows.

Is there some way of moving the cursor line along the formula, character by character, so that every element of the formula can be checked/edited within the formula window?

markly

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
A bit fiddly but you should be able to highlight the entire formula to select it. Press CTRL + C to copy it, paste into Notepad and edit it. Then copy the formula from Notepad and use CTRL + V to paste it into the CF formula bar.

I've tried it in XL 2000 and it works.

If you want to use the arrows to navigate the formula without it inserting references then press F2 before you try it.

Don’t know if this will help, but you can shorten your formula a bit:
Code:
``=AND(Z6=8,(COUNTIF(A6:CC6,8)+SUMPRODUCT(--(Z2:Z18=8),ROW(Z2:Z18))-6)+(OR(H2=8,H4=8,Q2=8,Q4=8))=1)``

Best way to edit though would just be to write the formula somewhere else and copy&paste it back in.

Just a little clarification to Glenn's suggestion. Whan you bring up the Conditional Formatting dialog, click in the formula first to get your cursor in there, then press F2. Then you should be able to use right/left arrows to move arround the formula without inserting strange cell references.

Thanks Glenn and Peter. Pressing F2 once the cursor line is in the formula, then using the arrow key, is definitely the solution. I knew there just had to be a simpler way than copying the formula somewhere else, editing it there, then sticking it back.

Replies
1
Views
366
Replies
4
Views
643
Replies
5
Views
455
Replies
8
Views
220
Replies
2
Views
480

1,217,382
Messages
6,136,239
Members
450,000
Latest member
jgp19

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back