Search Comments

Learn Excel from MrExcel, Episode 2103 - Search Comments

Hey, welcome back to MrExcel netcast, I'm Billy Jelen. Today's question is sent it by Todd. Is there some VBA that I could use to search comments in the workbook? And Todd is talking about these type of comments where we go to the Review tab insert a new comment and type something. Those comments are usually seen by the little red arrow there that pops up and Todd is looking for some VBA that it can search through all the sheets in his workbook to find those comments.

Why hey, it's even better than that. Let's just use one cell and we'll go to Ctrl F4 for find, find. I want to find all the comments that contain the word Apple. Apple, spell it right. And then click the Options tab. This is awesome, right. It's hidden. You have to click Options or you can't see it. And then look in formulas, values or I have no idea when they added this in but it's there - comments.  We will find all and there's a list of all of those cells on this worksheet.

Let's try it with the workbook. Find All. Sure enough, it finds all the comments in all of the sheets and now anything that has Apple in the comment is going to appear in yellow. Even applesauce will appear. All right.

So any form of the word Apple and we've effectively marked those cells in all of the sheets. Like this comment says banana, so it was not found by our trick. So a pretty cool feature that's built into Excel but hidden where no one would ever find it.

Hey, that tip and a lot more are in this book, POWER EXCEL with MrExcel: 617 Excel Mysteries Solved. Click that i on top right hand corner for information on how to buy the book.

All right. Episode recap. Todd is looking for a way to search in cell comments in Excel. It’s already built in. Use Ctrl F for the find dialog. Click the Option button to get some more features in the look and dropdown, change to comments. We then changed to workbook. Click Find All. And then a great trick that I use all the time, Ctrl A to select all of the found cells on the current worksheet, change the fill color so we can find them later and then click on the next sheet in the search results to move to the next sheet and repeat.

I want to thank Todd for sending that question. And I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.