Set up conditional formatting for an area using VBA in Microsoft Excel

In this article, we will create a macro to apply conditional formatting in the selected area.

We have used random numbers as sample data to demonstrate working of the macro.

RawData

Macro can be executed by clicking “Find Max” button on “Main” sheet.

ArrowMainSheet

Logic explanation

We have created “ConditionalFormat” macro to apply conditional formatting to the selected area. This macro highlights the cell in violet color, which contains the maximum value in the selected area.

Before executing this macro, one has to select an area on which he wants to apply the conditional formatting.

For example, we have selected a range of cells from B9 to F17 as the sample of selected data.

ArrowSelectedSample

On executing the macro after selecting sample area, conditional formatting is applied on the selected area and cell D16, which contains the highest value (97), will be highlighted in violet color.

ArrowFirstSampleOutput

Now, take another sample. We have selected the range from cell B10 to F13 as second sample area.

ArrowSecondSample

After selecting the second sample area, click on “Find Max” button to run the macro. Conditional formatting will be applied on the selected area and cell C11 will be highlighted in violet color, as it contains the maximum value (92) in the selected area.

ArrowSecondSampleOutput

After executing the macro for two different samples, conditional formatting is applied to both of them. In the sample data, both the cells C11 and D16 will be highlighted in violet color, as they represent maximum values in their respective samples.

ArrowFinalOutput

If values in any of the samples are changed and one of the newly inserted values is the highest value, then the cell with that particular value will be highlighted.

Code explanation

Selection.FormatConditions.Delete

Above code is used to delete any conditional formatting applied on the selected data.

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _

“=” & Selection.Cells(1).Address(False, False) & “=MAX(” & Selection.Address & “)”

Above code is used to add a new conditional formatting. Type specifies that expression is used to apply the condition formatting. Formula of the expression is defined in Formula1.

Selection.Cells(1).Address(False, False)

Above code is used to reference address of the first cell in the selection.

“=” & Selection.Cells(1).Address(False, False) & “=MAX(” & Selection.Address & “)”

Above code is used to compare the value of the first selected cell with the maximum value of the selection.

Selection.FormatConditions(1).Interior.ColorIndex = 39

Above code is used to assign violet color to the cell that satisfies the condition of the formatting.

 

Please follow below for the code


Sub ConditionalFormat()

With Selection
    'Delete any previous conditional formatting
    .FormatConditions.Delete
    
    'add conditional formatting to selected cell
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=" & Selection.Cells(1).Address(False, False) & "=MAX(" & Selection.Address & ")"
    
    'Assigning Violet color for the conditional formatting
    .FormatConditions(1).Interior.ColorIndex = 39
    
End With
   
   
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>

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