Check and color a cell if ... (VBA)

Hi, I was wondering if I could get help with a VBA project I'm working on to write a macro script that will check the string within a cell to see if conditions are met and then color the cell if any of them are met.

I have some programming experience, but I don't know the syntax of VBA very well. I'm having difficulty figuring out how to activate cells, shift down to test the next cell, etc.

There are 10 conditions, mainly of 3 types.
1) Is there a "Q" in the string
2) Are there multiple "C"s in the string
3) Are there more than 20 characters (ignore spaces) in the string

Essentially the macro will select cell "i2", test the cell text (containing a string of capital letters and spaces) for the 10 conditions, and color the cell background red if any conditions are met. Then it will check i3, i4, i5,...i500. Then it will move to k2, k3, k4....k500. M...O...(increment by 2 letters) until AC2...AC500.

My work so far:

I have a variable defined for each letter (ex: Ccount), a total length variable for checking total length, and an errorcount variable will increment from 0 if any conditions are met, and the cell will turn red if errorcount is >0 at the end of the cycle for each cell. Then it should set everything back to 0 and redo the process.


If someone could help with the outlining for the first 3 conditions, I can implement the others since they're very similar.


I already have the functions written out for each condition, but since the program will be checking hundreds of cells for all 10, I figure a loop in a macro in VBA would be more elegant. However, if there are suggestions for using my current functions (lots of len() ) those might be useful instead.

Hello cambridge,

That is a lot of conditions and a lot of cells to check. Each of the three conditions you mention require 3 separate methods to validate the condition. Here is an example...

Please Login or Register  to view this content.

If speed is an issue, I can write some routines using more advanced pattern matching techniques.

Hi cambridge;
Don't know if you're still looking for an answer.
The thread isn't marked Solved, but I don't want to waste your time if you're satisfied with Leith's reply.