How to Highlight Lowest or Highest Value in Excel

Highlight Lowest or Highest Value in your Data Set using VBA Script.

It is normally required to highlight the lowest value or highest value in set of data range when you are analyzing the data. Highlighting manually or using conditional formatting in large size of data becomes tedious job. 

In Excel you can write a simple macro which will identify the lowest of highest value in given range.

Let us have an example 

We have a data set of range from A1 to D10

 In each row we have a least value however it is difficult to identify manually. Writing a short script of VBA Code will help us to identify the value. 

In VBA using cell properties we can highlight lowest or highest value.

Lets learn how to write VBA Code in Excel

For every macro we need to have a logic and variable to declare explicitly.

In above pic we have declared 
ws as Worksheet, which will hold the object worksheet.
i, LastRow, minValue as long which will hold the values
cell, rng as Range which will hold the ranges of data set

 Now let us set the object and determine the last used row in data set.

Once you set the worksheet object as ws, you will have to determine last row.

Now using for - next loop start determining the lowest value in each row and highlight with Red color and background Green color

You can also find highest value by replacing with below code

minValue = Application.WorksheetFunction.Max(rng)

This will highlight the highest value in range of data
Once done with writing, now execute the code and see the result as displayed below

Hope you found the easiest method to highlight the minimum value or maximum value by writing few steps of code.

Download << Practice workbook >> on Google drive