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!