Tag Archives: VBA

How to set Microsoft Excel cell background color in VBA

Recently I have been working on a VBA project, so I will post some skills or tips I learned and used on Excel VBA program. Today I will share how to change the cell background color in the VBA module.

The property of background color is , and the color is used as Index format. So to setup a cell (row ID: 1, column ID: 12) in Red background color is the below:

Worksheets(1).Cells(1,12).Interior.ColorIndex = 3 ‘change the background color to RED

And you can find more color index in the rest of my post. Continue reading

How to auto find and replace text in bulk Word files and save convert to other format with script

Challenge:

As I indicated in my previous post, I needed to rename thousands Microsoft Word documents based on the updated old/new catalog ID pairs.

But that was just one part of my requirement. Since these Word DOC files have the old catalog IDs inside the content which need to be updated with new IDs as well. The methods of my previous post could only resolve the file name part but not the contents.

Unsuccessful trials

I started with some existing utilities I found on the Internet but they were not working as I hoped.

1), I tried the Useful File Utility along with its free plug-in “Batch Replacer for MS Word” (Useful File Utility is a shareware which can allow you to use 15 times for free, then $29 for a single user license). It looks promising as it indicated on its web page, but it failed on me with too many un-successful replaces in a few hundreds files.

2), I was impressed with this VBA macro I found on Internet named VBA Find and Replace. But it could not work well with the Find and Replace text list. I always get an error saying my name list is not valid.

After one night’s trying, I decided to write my own script to handle such automate job.

Continue reading