Highlight the bad stuff help!!

  • Thread starter Thread starter John
  • Start date Start date
J

John

Here's the deal.

In column M, I can enter either an "a", "b","c","d","e",or "f".

There are over 5000 entries in this column.

When I count up the total number of a's, b's etc. using COUNTIF, I get
4990.

Somewhere in my M column, there are 10 entries that are screwed up.

What I was trying to do was to (either using formulas or VBA),
highlight all the good cells in the M column and the ones containing
the bad entries would stand out (not being highlighted) because they
have an extra space, or a bad character, etc.

Hopefully, you can figure out what I am trying to do.

Help!

Thanks!



Stacy
 
Select your entire set of data in Column M, do Format / Conditional Formatting /
Change 'cell value is' to 'formula is' and paste in the following formula:-

=OR(M1="a",M1="b",M1="c",M1="d",M1="e",M1="f")=FALSE

Then hit the Format button and select a bright red pattern. Hit OK and then
just scan your data for any highlighted cells.

To check you have done it correctly, simply change a cell you can see to say z
and it should light up red.
 
Can you auto filter on that column? Then you can just
select the options that do not belong like blanks.
iolaire
 
Back
Top