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.
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.
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
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 firstname.lastname@example.org