How Can I Colour cells with VBA?

Indeed, VBA could be a super handy way of applying some conditional formatting on your data.

It has couple of advantages over the Excel built-in conditional formatting:

• It is more robust – cells additions or deletions are much less likely to cause unnoticed shifts in conditional formatting references

• It does not bloat your spreadsheet size

So, let’s check some scenarios when it can be used:
Scenario 1: You need to colour certain cells on the basis of their absolute values.

This one I found particularly useful when I was interpreting the statistical outputs for my master thesis. It is, by all means, much easier to spot significant values, if you’ve got them automatically highlighted, without having to manually examine your model.

Scenario 2: You need to colour a cell range on the basis of a value in a certain cell

This is where VBA-based conditional formatting truly beats Excel built-in formatting as it is simply not possible to do this with the latter.

The code below quicly and easily higlights in red all the values in columns A to J for the reviews that have had a “Helpfullness Denominator” > 10

Keen on revamping your condtional formatting?

Happy VBA coding!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.