Keep Conditional Formatting

[EmmaG1959]

In order to highlight the changes I have made in a customer's data, I am going to add a number of columns (containing 0 or 1, generated in a database). The cell will change colour if there is a '1' in the corresponding new column. I hope this makes sense!

However, I'd like to return the spreadsheet to the customer without the new columns. Is there any way of telling the formatting (i.e. background colour) to become 'hardwired' and delete the extra columns?

TIA
Emma

[mdbct]

You could use a macro to set the color based on the new column's value or you could use Auto Filtering and then change the color on the visible cells.

For The Auto Filter method:

1. Apply the filter and set your criteria
2. Highlight the cells whose color you want to change
3. Press Alt + ; (semi-colon) to select only the visible cells.
4. Set you color.
5. Remove the Auto Filter.

[ChemistB]

How about simply hiding those columns so the customer doesn't see them? You can lock the spreadsheet so they don't unhide

[EmmaG1959]

Hiding columns not really an option. This is the customer's own data and will be fed back into their database.

Perhaps this is the moment to learn macros?

Meanwhile, I will have a go at the Auto Filter in the morning... Thanks!

[ChemistB]

If you are using a formula to generate these 1's and 0's, you should be able to modify that formula and use it in the Conditional Formatting directly without the other column. What's the formula?

[EmmaG1959]

If you are using a formula to generate these 1's and 0's, you should be able to modify that formula and use it in the Conditional Formatting directly without the other column. What's the formula?

Unfortunately I did all that in Filemaker (Mac-based database software), where I am much more at home than in Excel! I have lovely coloured fields in Filemaker, but while I can export the data, I can't export the conditional formatting, so I have to re-create it in Excel.

A bit stressed with 30,000 things to do right now, please keep the ideas coming, I will have to do this quite soon...

[ChemistB]

Okay, if you give the criteria for those 1's and 0's, we should be able to come up with a conditional formatting for you that doesn't require that extra column.

[EmmaG1959]

They are based on 2 tables - a straight comparison between fields, e.g.

Product Code[tab]Name[tab]Description[tab]Weight (there are actually about 30 fields!)

- it relates the tables on Product Code, then if old Name ≠ new Name it puts a 1 in 'NameChange' field, etc. Then I use that '1' to tell Excel to colour the background of the cell to highlight that the data has been changed.

I don't think I can do the comparison between 2 spreadsheets, as items have been added and deleted, and I'm not allowed to re-sort by Product Code.

Why are my problems never straightforward? No worries though, I've warned them about the extra columns and they're happy with that. Would be nice to be able to do though.