Need to select Excel rows by colour 1

[zbnet]

Someone has 'gifted' me a worksheet that's pretending to be a database. This is an old data set, and they have helpfully updated the sheet with add, mods and deletes. However, instead of just deleting the deletes, they've changed the font colour to red.

I need to go through and delete all the red rows. But they're scattered throughout hundreds of rows, and I don't fancy doing it manually (it'll take hours). I've tried identifying the colour of the rows formulaically so that I can sort them all together, but I can't seem to find anything to do this (Excel 2K).

Can anyone help? Am I missing something obvious?

 

[xlbo]

No standard functionality for this I am afraid

Function CI(rng as range)
CI = rng.font.colorindex
End Function

Copy and paste the above code into a standard module
In the next empty column enter
=CI(A2)

This should return you the font colour as a number. You can then use the autofilter , setting the criteria to 3 (red) and delete all the red rows in 1 go

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[zbnet]

Top stuff! Works a treat - many thanks, Geoff, you've saved me hours!