Excel Formatting Spill-over 1

[MToomey]

Hi folks,

I'm working on a large-scale revision of a special project based on customer/structural comments.
I have a spreadsheet to track what tasks are completed. First column is drawing number, subsequent columns are comments on each particular drawing. This leaves one task per cell, organized by drawing number.
As each task when is completed, I will fill the cell with a highlight color, and highlight the drawing number when that drawing is complete and ready for review.
Due to the nature of the commenting process, I will get comments on a drawing that require changes to another drawing that wasn't on the list. As I work through the comments and discover a drawing that needs to be added, I will insert a row where that drawing belongs alpha-numerically and add the relevant info.

Here's my issue: when I add a new row, excel decides that I want the same cells filled as above or below. I want the cells to not be filled because they are not complete yet. So I select all the highlighted cells in the new row and fill them with no color. Then I type the drawing number and comments in the now blank cells, and as soon as I hit tab, it re-fills them with the color.
Sure, I can just manually un-fill them each time, but it's getting aggravating. Surely there must be a way to change this behavior.

EDIT: I'm running the Office 16 version of excel if that changes any answers.

Any thoughts would be appreciated.
Matt Toomey

 

[Stick.]

I don't know how to stop the autoformat for new rows, but what I'd do here is add a status column, fill that in with words like "in progress", "complete" or whatever, and use that to drive some conditional formatting to highlight cells however you want. Add some data validation dropdowns to the status column if you want, although that's probably more trouble than it's worth here. The conditional formatting should get copied to inserted rows, but that's fine since the data stored in the status column won't, so nothing will get highlighted until you fill that cell in. This has the added advantage of not using formatting to store data (data being completion status here), which I don't like since formatting is too easy to accidentally change. You can also filter the list by completion status, and maybe even get really fancy and make a pivot chart to show percent complete or whatever.

 

[IRstuff]

Not necessarily any simpler, but you could create a template row with formatting turned off, and copy/insert row for the added drawing.

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[3DDave]

You are inserting a row into a region - Excel will do just what you say it does.

If you add data to a blank row at the end and then sort the row will be placed as desired with the default formatting.

Other than that - you can insert the row, then copy/paste a blank row and to overwrite the formatting.

I would tend to structure this more like a database - a separate sheet with the drawing number, the comments, a date or other flag about completion and then the display sheet uses references and conditional formatting to determine the coloring.

 

[MToomey]

Thanks folks,

Barring any new revelations, I think that just about covers it. Excel is being excel, and there is no simple way to change that.
Stick - I do appreciate your point about formatting storing data. I hadn't considered it that way before, but I see exactly what you mean (especially in light of the question that brought up that answer!).

 

[CWB1]

I believe the button or quick key you're looking for is "clear formatting."

 

[pmover]

perhaps this link will help you:

https://answers.microsoft.com/en-us...row-from/f6e8015b-e15e-4e51-9348-42a038a9946c