Conditional Formatting using VBA in Microsoft Excel

Let’s paint a Monalisa painting and leave Picasso behind using VBA. Ready, Get Set …

Wait! Wait! Hold your horses. We are just going to use VBA to do conditional formatting and it’s easier than a painting that ? shaped bird.

The Generic syntax of VBA Conditional Formatting

If condition than
Range(range).Interior.ColorIndex= 1-56

Give your condition check at the condition and then format your range using property .iteriour.colorindex of Range object. The color index has 56 colors. Go play Holi with them and explore which number holds which color.
Now let’s understand it with an example

Example: VBA Code To Conditionally Format Cells

1

So, this is the scenario, we have a list of random people with their age and age group.
How easy it be if could tell the age group a person by just looking at their names.
Now to do this, I want VBA format Name RED if he is an adult, YELLOW if he is a teenager and GREEN if he is a KID and nothing if the cell is blank.

Sub FormatUsingVBA()
Dim rng As Range
Dim lastRow As Long
lastRow = Cells(Rows.Count, 3).End(xlUp).Row
Set rng = Range("C2:C" & lastRow)For Each cell In rng
If cell.Value2 = "Adult" Then
Range(cell.Address).Offset(0, -2).Interior.ColorIndex = 3
ElseIf cell.Value2 = "KID" Then
Range(cell.Address).Offset(0, -2).Interior.ColorIndex = 4
ElseIf cell.Value2 = "Teenager" Then
Range(cell.Address).Offset(0, -2).Interior.ColorIndex = 6
Else
Range(cell.Address).Offset(0, -2).Interior.ColorIndex = 0
End If
Next cell
End Sub

To run this code right from the sheet, I have drawn a button on the same sheet and assigned this macro to it. Now, whenever you will click the “Format” button your code will run and update name cells with color depending on their age group. How? Let’s find out.

Code Explanation:


Dim rng As RangeDim lastRow As LongThese two lines are variable declarations.
rng for Range that holds the age group and lastRow to get last nonblank row number.
lastRow = Cells(Rows.Count, 3).End(xlUp).RowThis line returns last row number in lastRow Variable.
Set rng = Range("C2:C" & lastRow)This line sets the range starting from C2 and to the last row.
It makes your code dynamic. You add new rows to your data and it will detect and will store in new range in rng variable.
For Each cell In rng
If cell.Value2 = "Adult" Then
Range(cell.Address).Offset(0, -2).Interior.ColorIndex = 3
ElseIf cell.Value2 = "KID" Then
Range(cell.Address).Offset(0, -2).Interior.ColorIndex = 4
ElseIf cell.Value2 = "Teenager" Then
Range(cell.Address).Offset(0, -2).Interior.ColorIndex = 6
Else
Range(cell.Address).Offset(0, -2).Interior.ColorIndex = 0
End If
Next cellThis is the main segment.
For Each cell In rng
The first line runs the loop to all cell in your range.If cell.Value2 = "Adult" Then
Next line is condition check. It checks if the current cell value is Adult or not.
If yes then next line runs, else it skips to next if statement.Range(cell.Address).Offset(0, -2).Interior.ColorIndex = 3 Now if IF condition returns TRUE, then this line sets the cell color to ColorIndex 3 that is for RED. Similarly following IF statements run and take action as specified.

So yeah, you can conditionally format ranges using VBA. Interior method of Range object controls many other properties for formatting. You should toy with them, it won’t do any harm but you will learn definitely. And if you face any difficulty than what I am here for. Ask your questions in the comments section.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube