Excel question...?

Question

I would like a way to automatically mark (color, add a character to the beginning of the contents, whatever...) every cell in a worksheet that matches a particular value.

I have a spreadsheet that is 8000 lines long and I have to look up the contents of each of the cells. Some of the cells are repeated, so I would only have to look them up once, but as soon as I passed 100, I couldn't remember what I had seen already. Is there a formula or a macro I can use to mark the cells that match the ones I've already seen(even if I have to add the entry manually every time) such as "find all the cells that have contents = "bear" and make them yellow (or prepend an xxxxx to the contents, etc)?

Thanks!

Answer 1

You can use "conditional formatting" found under the "Format" menu bar. However, you are limited to 3 conditions which means 3 colors.

If you need more than 3 colors, then you need to write a macro.

Also, there's the brute force techniques
1. you seach the spreadsheet and do a find and replace.
2. sort the spreadsheet by the common cells so they're all grouped together.
3. use a lookup table, vlookup or hlookup.

Without having to spend time writing a macro and you need more than 3 colors, I would probably just sort the spreadsheet such that the common names are grouped together.

Answer 2

Yes, there is a way to do this. Go to format, then click on "conditional formatting".

For example, if you wanted any cells whose contents contain "bear" to be yellow, enter that as your condition, then use yellow as the color for formatting, and you should be good to go.

Answer 3

a quick and easy solution is this: do a find and replace ... 'find' your item ...'bear' and then have it replaced with the same but change the formatting to color the cell. IM me if you need further information. Thanks!