26 Apr 2020

VBA can color certain characters within a cell. It was fun to practice vba and build this but it doesn’t work well with many rows of text.

 

 

Why Do This?

It makes key words standout. Helpful when scanning text. Letters qui are highlighted below.

 

 

 

 

 

 

 

 

Excel File

Follow along with my Excel file (best to save it and then open it). It does contain macros (vba).

 

 

VBA Inputs

The code takes several inputs. Below you see the main 2 inputs. The grey button runs the code.

 

 

 

 

 

What is [Color 7]?

I was too lazy to create a list of color names. Column L contains a list you can reference.

 

The code behind this comes from:

http://dmcritchie.mvps.org/excel/colors.htm#colorindex

 

 

 

 

My code also includes an option to use a random letter and color. Just for fun!

 

 

 

 

 

 

 

 

Code Too Slow?

It searches for all occurences of the search value in a cell. Then it goes to the next cell down and repeats this search. Text length X number of cells determines the time to run.

It’s dangerous to run this code on thousands of rows as it could take a long time to complete. Setting Application.ScreenUpdating = FALSE makes it run faster (we don’t see any results until it’s all finished) but it can still be super slow on a large dataset.

 

 

Alternative Solutions

A conditional formatting rule (based on a formula) can highlight the entire cell if the search value exists inside it. True, the entire cell would have the color (not just exact text search value) but it’s easier & quicker than a vba solution.

=AND(‘vba Parameters’!$E$32=TRUE,ISNUMBER(SEARCH(‘vba Parameters’!$E$22,A2)))

 

The AND function contains two parts. Both must evaluate to TRUE in order to trigger the rule.

‘vba Parameters’!$E$32=TRUE

above is a switch to allow you to turn off the rule (even if the 2nd part below is true)

ISNUMBER(SEARCH(‘vba Parameters’!$E$22,A2))

above is the core functionality that looks for the search word inside the current row’s cell (A2).

 

 

About Me

My name is Kevin Lehrbass. I’m a Data Analyst. I live in Markham Ontario Canada.

A good way to keep my mind busy during these difficult times is to play around in Excel and also in Power BI. Exercising is also good and I’m doing that as well (but it’s not as fun).

 

Post a comment