We'd like to remind Forumites to please avoid political debate on the Forum... Read More »
We're aware that some users are experiencing technical issues which the team are working to resolve. See the Community Noticeboard for more info. Thank you for your patience.
📨 Have you signed up to the Forum's new Email Digest yet? Get a selection of trending threads sent straight to your inbox daily, weekly or monthly!
Excel Conditional Formatting
Options
![[Deleted User]](https://us-noi.v-cdn.net/6031891/uploads/defaultavatar/nFA7H6UNOO0N5.jpg)
[Deleted User]
Posts: 0 Newbie


in Techie Stuff
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?
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?
0
Comments
-
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.Stompa0
-
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"0 -
If you take out the $ signs in the original formula you should get what you need.:wall: Flagellation, necrophilia and bestiality - Am I flogging a dead horse? :wall:
Any posts are my opinion and only that. Please read at your own risk.0 -
Hmmm, I see what you mean. Have you tried using named ranges?Stompa0
-
-
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.)0 -
have you tried using the format painter?Apparently I'm 10 years old on MSE. Happy birthday to me...etc0
-
-
How about $K:$K - format the whole column?0
-
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.0
This discussion has been closed.
Confirm your email address to Create Threads and Reply

Categories
- All Categories
- 351K Banking & Borrowing
- 253.1K Reduce Debt & Boost Income
- 453.6K Spending & Discounts
- 244K Work, Benefits & Business
- 598.9K Mortgages, Homes & Bills
- 176.9K Life & Family
- 257.3K Travel & Transport
- 1.5M Hobbies & Leisure
- 16.1K Discuss & Feedback
- 37.6K Read-Only Boards