MS Excel advanced help please

[wh666-666]

Hi sharkies ..

Wondering if anyone can help out. At the moment im building a spreadsheet workbook up with a few features, intended to be passed to other people with limited knowledge, so therefor, I want it to do certain things automatically.

I already have the first worksheet handled, with macros implemented and the top few rows frozen as a pane.

However Ive got two niggling issues with the second worksheet:

- Ive got a horizontal few rows, with column title information in and then beside this running vertically down I have a legend explaining colours and technical terms. I would like the selection where the data is, for example c4:f299 to be scrollable, but the legend on the side and the header row at the top to stay where they are.

Ive tried freezing panes, even experimentally on one row and one column, which MS state can be done, but the column doesnt get frozen. Ive tried using splits but yet the column still scrolls and ive tried worksheet properties to set a scrollable area, yet this doesnt work either.

Im at a bit of a loss as to how to sort this as I need three scrollable columns of data but a legend beside it and a row as a header.


- Second problem is conditional formatting. I have five different colours (including no fill) that the formatting needs to paint automatically across three cells in a row.

For example data in D5:F5, I need it to autoformat fill colour in blue if it detects the same value (a name) in D5 and highlight all three cells on both records. I need it to fill colour orange if it detects the value 'none' from a dropdown in E5 and 'Y' in a dropdown in F5, but colour it red if it detects 'none' in E5 but 'N' (none) in F5. Also data in any row, that detects a 'N' (none) in F5 and any value other than 'none' in E5, needs to be filled in a green colour, again spanning the three cells.

All Ive been able to do so far is colour one cell dependant upon the value in E5. I need it to be more advanced though. Also I need this to be automatic so if extra data is inserted or altered in the future, it will automatically do the conditional formatting itself.



Thanks for reading, comments and help would be great ....