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.

Sub ColourCellsonAbsoluteValues()
Dim Cell As Range
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'written by Angelina Teneva, September 2016
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For Each Cell In Selection
Cell.Activate
If Cell.Value < 0 Then
Cell.Font.Color = RGB(255, 0, 0) 'Makes negative cells red
Else
Cell.Font.Color = xlNone
End If
If Abs(Cell.Value) > 1.96 Then Cell.Interior.Color = RGB(0, 255, 204)
Next Cell
End Sub

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

Sub ColourCellsin_DifferentColumn()
Dim Cell As Range
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'written by Angelina Teneva, September 2016
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
For Each Cell In Range("F5:F" & ActiveSheet.UsedRange.Rows.Count)
Cell.Activate
Range(ActiveCell.Offset(0, -5), ActiveCell.Offset(0, 4)).Font.Color = RGB(0, 0, 0) 'black (reset any previous formatting)
If ActiveCell.Value > 10 Then Range(ActiveCell.Offset(0, -5), ActiveCell.Offset(0, 4)).Font.Color = RGB(255, 0, 0) 'red
Next Cell
End Sub

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.