Forum Discussion
Conditional Formatting - a column
Hi. I want to check a column for certain words e.g. cat, dog, mule, chicken, horse and if the searched for word is found then I want to format it a certain way. E.G. cat (dark blue background, white text), dog (beige background, black text, and so on. How can I do that or can I do that. I know I can use conditional formatting and containing specific text option. I find the cell references get muddled over time. Is there a function which will let me look up a format depending on value? Like a sort of
if x then (background, font colour and bold or italic), if y then ...... if q then ..... and so on. Do I need some sort of named range or can I not do this? Thanks. Dennis
4 Replies
- As you mentioned you can use Conditional Formatting to look for specific text and format accordingly. As for getting messed up over time, excel tries hard but inserts, deletes, moves and such can mess up the conditional formatting range. As for alternative options, your only other choice for automatically changing formatting is to use a macro. Maybe instead look at what you are doing in the sheet (inserting/deleting/moving) and see if you could rearrange or reformat the sheet to avoid that. Maybe if you format the range as a table that could help. Or maybe if you have an 'input' sheet and then another sheet that formats and displays the data. In fact if you have an input sheet you can add a 'delete' column and enter a date that value was 'removed' and your output sheet wouldn't show it any more but you input sheet would even capture historical tracking information. Since I have no idea what your actual sheet is it is hard to say what might be best in your case.
Thanks for your reply. In truth I'm at best an occasional user and the wishes / wants detailed in the file are more hopes I can do something rather than sound expectations!
DennisMetro so in the file I saw how the formatting for col C got broken up but col B formatting (because it was set to the whole column) wasn't a problem. I updated all the formatting rules to apply to the entire column so hopefully that will work smoother. I only had to modify a couple rules to exclude the blank cells.
