Editing long conditional formatting formulae

markly

New Member
Joined
Aug 14, 2007
Messages
6
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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,510
If you want to use the arrows to navigate the formula without it inserting references then press F2 before you try it.
 

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
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.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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.
 

markly

New Member
Joined
Aug 14, 2007
Messages
6
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. :biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,122,371
Messages
5,595,784
Members
414,020
Latest member
Meghdad

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?

Disable AdBlock

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
Top