Excel 2007 - unusual row formatting requirement I need help with.

I have a large table (many rows and columns, and the numbers of both change from time to time). I want to have a column - call it "highlight" - such that an 'x' in the column automatically changes the background color of the whole table row in which the 'x' appears to something distinct. This row highlighting would be persistent, meaning that it remains until the 'x' is deleted.

Ideally, rather than a simple 'x,' the mark might be, say, 'y' to change the background to yellow, or 'r' to change it to red, etc. I can live with changing the background for the whole SHEET row, rather than just the TABLE row if that's necessary. It should be possible to highlight more than one row.

I've tried all of the usual formatting techniques (I think) but maybe not. Suffice it to say I'm comfortable enough with Excel to work in that arena if asomebody points the way.

I realize that accomplishing what I want may be an easy thing to do in VBA (maybe an if-else mechanism, or a case-of mechanism for this?), but I'm less comfortable in that environment. If that's the only way to do it however, so be it. I think I've played with VBA enough to 'maintain' a formula I've been provided with, and even modify it, but not enough to write one of any sophistication myself.

Any help appreciated. thanks in advance.  - Jim

Jim

This is just the sort of job that "Conditional Formatting" is good for. In the conditional format you need to use a formula to control the format.

If your highlight column is C, then a typical conditional format formula would be something along the lines of =INDEX($C:$C,ROW())="x". This conditional format needs to be copied to all the cells in the table.

Peter

Peter

Thank you. It looks like that will do it and I will try it.

To be honest, I'd not pursued the color each cell individually approach you've provided, thinking there must be a more elegant solution in Excel. Is it the case that there's no 'wholesale' way to color a row as a unit without populating every cell in the table with a formatting formula? (With 5-6,000 cells involved, it seems worth asking...)

Jim

Jim

With a few thousand cells, my guess is that the conditional formatting approach will not create too much of a performance hit.

A way of increasing the elegance is to create a name to represent the conditional format  formula and then use the name in the conditional format. If you do that, then you only have to edit the name if for example your control column changes.

Of course, VBA solutions are also possible using the Worksheet_Change and Worksheet_Activate event handlers but they are probably harder to maintain if you are not used to working with VBA.

My own approach to this sort of thing is to make use of the Autofilter to show only the rows that I'm interested in rather than using colour coding of cells and scrolling down a table to see the highlighted rows.

Peter

Once again, thanks - your advice is appreciated and I will do the 'every cell' thing.'

I do use filtering to bring relevant rows together for examination, but the highlighter function has a different goal: that is to firmly identify a row for minor data revisions, maybe 30 columns to the right of the unique row indentifier (e.g. employee name in column A).

Once the row's highlighted (lit?), you can make all sorts of navigational errors motoring to the 30th column and still be sure you land at the right cell. It's quicker than freezing the window and scrolling over, less cumbersome than scrolloing 'down' to get the correct row just below the column heads. And it's persistent.

Thanks again for the help. My question's answered.

Jim

Peter -

The solution above worked well, and no performance hit (tnx again).

I now have a more interesting problem I'd like you to look at. I'd detail it here, but that would muck up this otherwise closed stream. If I'm not obligated to simply drop my question in the general pool for anyone to answer, how do I correctly aim it at you? (Feel free to demur if you want to.)

Jim

Jim

I think that you should be able to email me directrly through this site.

Peter