Excel VBA – Search and highlight text

Sometimes we have an excel file, where we want to highlight anything, matching a given cell. Without using the search & highlight option, if possible. Like this:


Thus, in this case, whenever something in range A1 is written, the values in E1:E10 are highlighted, if they contain the substring of the A1.

How is this achieved with the code above? There is a Worksheet_Change event, which calls the SelectAndChange procedure. It contains two nested loops, which check per cell (the outer one) and then per character in the cell, whether value of the cell contains a substring, as in A1.

Once such a value is found it is highlighted in red. Cheers! 🙂

Tagged with: , , ,