Excel Conditional Formatting

[Deleted User]

I have a spreadsheet with several columns formatted with data bars, so that each cell is colour filled in proportion to the value of that cell as a proportion of the maximum value in the whole column.

If I insert a new line in the sheet by copying an existing line and editing the cell contents, all is fine except that it's not copying the format rules. Instead of the new line formatting cells according to the percentage of the column maximum like all the other lines, it's just comparing each cell with itself and colour filling 100%.

I can edit and correct the rules with format manager, but it takes about 20 mins every time I put just one new line in. I've just tried using right click to insert a new line and copy all in one step, but that makes no difference.

It's a bit of a bind, has anyone got any ideas?

Stompa

It would be easier to figure out what's going on if you could tell us the formula you're using. But it sounds like a relative/absolute references issue of some sort.

[Deleted User]

Format Style = "Data Bar"
Minimum = "Automatic"
Maximum = "Automatic"
Applies to = "=$K$13:$K$145" for example.

The problem is that the "Applies to" range gets broken in half either side of the new line, so that the cell in the new line has itself as the "applies to" range. So for example, if I insert a line between 22 and 23 I'll get:

Applies to = "=$K$13:$K$22,$K$24:$K$146"

and the new line:

Applies to = "=$K$23"

rmg1

If you take out the $ signs in the original formula you should get what you need.

Stompa

Hmmm, I see what you mean. Have you tried using named ranges?

[Deleted User]

rmg1 wrote: »

If you take out the $ signs in the original formula you should get what you need.

That doesn't work. (BTW If the dollar signs aren't necessary, what on earth does it put them all in for?)

[Deleted User]

Stompa wrote: »

Hmmm, I see what you mean. Have you tried using named ranges?

No, I try to keep named cells to a minimum, it's too much aggro thinking up hundreds of them. (Editing the "applies to" box would be alot less aggro if the arrow keys moved the cursor in the dialog box rather than the cursor on the spreadsheet.)

stevemcol

have you tried using the format painter?

[Deleted User]

stevemcol wrote: »

have you tried using the format painter?

Just did, that doesn't work either....:(

paddyrg

How about $K:$K - format the whole column?

[Deleted User]

paddyrg wrote: »

How about $K:$K - format the whole column?

Not quite sure what you're getting at, isn't that what I'm already doing?

After adding the new line I select the whole column and re enter the range of cells for the formatting to apply to. The problem is that by the time I've done the whole spreadsheet it takes about 20 minutes. What I need is a way to prevent it from dividing the "applies to" range in half when the new line is inserted.