Conditional Formatting using VBA in Microsoft Excel

 

In this article, we will create a macro for conditional formatting of the raw data, based on the criteria specified by the user.

Raw data consists of leads generated by team members on a particular day. We want to highlight cells under leads generated column, based on the defined criteria. Conditions are specified for this raw data; if number of leads generated is less than 60, then the cell will be highlighted in Red color. Similarly, if number of leads generated is greater than 80, then the cell will be highlighted in Green color. Otherwise, the cell will be highlighted in Yellow color.

ArrowRawData

Macro can be executed by clicking the “Submit” button. Macro will do the formatting of cells in the “Leads generated” column, based on the value specified by a user in the indicator section.

ArrowAfterRunningMacro

Logic explanation

We have created “cmdResult_Click” macro which performs the conditional formatting of leads generated. Macro takes the minimum value from cell “F9” and maximum value from cell “H9”.

A user can change the value in cells F9 and H9, to change the minimum and maximum values.

FOR loop is used to loop from 13th row to last row of the worksheet and IF…ELSE statement is used to apply conditional formatting, based on the specified criteria.

 
Please follow below for the code

Sub cmdResult_Click()

'Declaring variables
Dim Val, Min, Max As Integer
Dim LastRow As Long

'Initializing the minimum and maximum value
Min = Range("F9").Value
Max = Range("H9").Value

'Finding the last row of the worksheet
LastRow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row

'Looping from 13th row to last row
For i = 13 To LastRow
    
    'Getting the value of leads generated
    Val = Cells(i, 3).Value
            
    'Changing the color of the cell
    With Cells(i, 3).Interior
        'Checking the condition and assigning the appropriate color
        If Val < Min Then
            .ColorIndex = 3
        ElseIf Val > Max Then
            .ColorIndex = 4
        Else
            .ColorIndex = 6
        End If
    End With
    
Next i

End Sub

If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at info@exceltip.com



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>