If range A = x/y/z colour adjacent cell

hi,

After some help with a macro.

I have a spreadsheet that tracks staff, in column F the value if either empty, "OK" or other

if a cell in column F1 -> Last Row is empty the cell to its left remains uncoloured, if it is "OK" the cell to the left is green, and if the cells is anything else then the cell to the left should be red.

it works with standard conditional formatting but everytime a new row is inserted it breaks the CF up into more than one rule and over time it gets very clogged up and sometimes breaks.

I've tried to get bits of code of the new to make this myself but im getting no where, so i would greatly appreciate it if someone could create a working macro that does this on the fly.

Much appreciated.

Originally Posted by Palmetto

Rather than a macro, try this first (on a back up copy)

1. Select all of the cells and clear the conditional formatting
2. Select all of the cells, again if needed, and re-apply your conditional formatting
3. Convert the data range into an Excel Table: Menu > Insert > Table ( you should be using a header row )

The Table will automatically extend all formatting and formulas to new rows and you shouldn't encounter the issue you described above.

Done step by step and its still breaking the CF up, also one thing this does mess up is the cell merge, columns f,g,h are all merged in that range.

Ok, ive found some code online that does exactly what i need: http://www.rondebruin.nl/find.htm

ive managed to modify to the code to find what i want and turn the adjacent cell green, but how can i get it to do the check for an empty cell and to check if its anything other than empty or "OK"

Please Login or Register  to view this content.