Calculations Conditional Formatting Counting with VBA

How to perform calculations in a range of cells, do a conditional formatting and count the conditional formatted cells with VBA. Watch the training video:

Watch this video on YouTube.

Complete VBA code:

Sub calculations()
Dim cell As Range
Dim count As Long
count = 0
For Each cell In ActiveSheet.Range(“A2:A5”)
cell.Offset(0, 1).Value = cell.Value + cell.Value * 35 / 100
If cell.Offset(0, 1).Value >= 10 Then
cell.Offset(0, 1).Interior.Color = vbRed
count = count + 1
End If

Next cell

Range(“A6”).Value = Application.Sum(Range(“A2:A5”))
Range(“B6”).Value = Application.Sum(Range(“B2:B5”))
MsgBox “Number of values greater than 10 is ” & count
End Sub
Sub removeColor()
Dim cell As Range
For Each cell In ActiveSheet.Range(“B2:B5”)
cell.Interior.Color = vbWhite
Next cell
End Sub

Published by

Dinesh Kumar Takyar

Welcome to! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: For a structured Excel VBA training course online you can visit:

2 thoughts on “Calculations Conditional Formatting Counting with VBA”

Leave a Reply

Your email address will not be published. Required fields are marked *

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