Highlight greater than values with ONLYOFFICE macro

14 July 2022By Serge

Quite often, working with spreadsheets might seem tedious and unrewarding. And that’s when macros come in handy. These little scripts help automate routine tasks and enhance our productivity.

In this blog post, we will talk about a small macro that highlights all the greater than or lower than values.

Reference macro

For reference, we used a VBA macro. This macro invokes an input box where you put the greater than value, and then it searches the selection on the spreadsheet and highlights the targeted cells.

The macro code is the following:

 Sub HighlightGreaterThanValues()
Dim i As Integer
i = InputBox("Enter Greater Than Value", "Enter Value")
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlGreater, Formula1:=i
Selection.FormatConditions(Selection.FormatConditions.Count).S
tFirstPriority
With Selection.FormatConditions(1)
.Font.Color = RGB(0, 0, 0)
.Interior.Color = RGB(31, 218, 154)
End With
End Sub

However, ONLYOFFICE macros are JavaScript based, which means they are more versatile and we will use different approaches to tackle this task.

Highlight values with a single color

That would be a simpler option. But on the other hand, this macro can highlight all the lower than values as well. In this case, you can highlight all the greater than values with one color and all the lower than values with another.

So, let’s get started! First, we need to get the active sheet. We achieve that by using the Api.ActiveSheet method. It returns an object that represents an active sheet. And then we target the selection on the spreadsheet:

(function () 
{
    var activeSheet = Api.ActiveSheet;
    var selection = activeSheet.Selection;
})();

Now we need to get a value of each cell from the selection. This time we will use the ForEach method. It executes a provided function once for each cell:

(function () 
{
    var activeSheet = Api.ActiveSheet;
    var selection = activeSheet.Selection;
    selection.ForEach(function (range) {
        var value = range.GetValue();
  });
    
})();

Then we add an if statement that will check if the values in the selection are greater than a certain number. In our case, it’s 5. If they are greater than 5, we change the cells’ fill color by using the SetFillColor method. It creates a fill color from the RGB code:

(function () 
{
    var activeSheet = Api.ActiveSheet;
    var selection = activeSheet.Selection;
    selection.ForEach(function (range) {
        var value = range.GetValue();
        if (value &gt; 5) {
            range.SetFillColor(Api.CreateColorFromRGB(255,0,255));
        }
    });

Let’s run our macro.

Highlight greater than values with ONLYOFFICE macro
Alternatively, you can change the font color of the targeted values. To achieve that, we will replace the SetFillColor method with the SetFontColor:

Highlight greater than values with ONLYOFFICE macro
To highlight the lower than values, we change the if statement parameter. Now, if the value of a cell in the selection is lower than 5, the cell background color will change.

(function () 
{
    var activeSheet = Api.ActiveSheet;
    var selection = activeSheet.Selection;
    selection.ForEach(function (range) {
        var value = range.GetValue();
        if (value &lt; 5) {
            range.SetFillColor(Api.CreateColorFromRGB(255, 255, 0));
        }
    });
    
})();

Highlight greater than values with ONLYOFFICE macro

Highlight values with multiple colors

Now, let’s take it up a notch and create a macro that will highlight the targeted cells with multiple colors.

First, we need these unique colors. We use CreateColorFromRGB method to generate them and we store these colors in the uniqueColors array:

var uniqueColors = [Api.CreateColorFromRGB(255, 255, 0),
        Api.CreateColorFromRGB(204, 204, 255),
        Api.CreateColorFromRGB(0, 255, 0),
        Api.CreateColorFromRGB(0, 128, 128),
        Api.CreateColorFromRGB(192, 192, 192),
        Api.CreateColorFromRGB(255, 204, 0),
        Api.CreateColorFromRGB(255, 0, 0),
        Api.CreateColorFromRGB(0, 255, 255),
        Api.CreateColorFromRGB(255, 0, 255),
        Api.CreateColorFromRGB(218, 128, 128),
        Api.CreateColorFromRGB(128, 0, 0),
        Api.CreateColorFromRGB(128, 0, 128),
        Api.CreateColorFromRGB(0, 0, 128),
        Api.CreateColorFromRGB(165, 42, 42),
        Api.CreateColorFromRGB(220, 20, 60),
        Api.CreateColorFromRGB(255, 99, 71),
        Api.CreateColorFromRGB(205, 92, 92),
        Api.CreateColorFromRGB(240, 128, 128),
        Api.CreateColorFromRGB(255, 165, 0),
        Api.CreateColorFromRGB(255, 215, 0),
        Api.CreateColorFromRGB(184, 134, 11),
        Api.CreateColorFromRGB(218, 165, 32),
        Api.CreateColorFromRGB(173, 255, 47),
        Api.CreateColorFromRGB(0, 100, 0),
        Api.CreateColorFromRGB(50, 205, 50),
        Api.CreateColorFromRGB(70, 130, 80),
        Api.CreateColorFromRGB(75, 0, 130),
        Api.CreateColorFromRGB(72, 61, 139),
        Api.CreateColorFromRGB(255, 182, 193),
        Api.CreateColorFromRGB(188, 143, 143),
        Api.CreateColorFromRGB(112, 128, 144),
        Api.CreateColorFromRGB(210, 180, 140),
        Api.CreateColorFromRGB(105, 105, 105),
        Api.CreateColorFromRGB(216, 191, 216),
        Api.CreateColorFromRGB(240, 248, 255),
        Api.CreateColorFromRGB(102, 205, 170),
        Api.CreateColorFromRGB(255, 240, 245)];

Then we declare the uniqueColorIndex variable and set it to 0. We will use this variable as an index to select unique colors from the array. To do that, we add an if statement that will go through the array, and increment the index:

var uniqueColorIndex = 0;
        
        function getColor() {
            
            if (uniqueColorIndex === uniqueColors.length) {
                uniqueColorIndex = 0;
            }
            return uniqueColors[uniqueColorIndex++];

Then, we need an if statement that will find the targeted values and apply the unique color to the cells. The concept is similar to the previous macro. We target the active sheet. Then, we get the values of the cells in the selection. And we add an if statement that will look for the greater than or lower than values and highlight them with the SetFillColor method. But this time, the color will be generated with the getColor function:

    var activeSheet = Api.ActiveSheet;
    var selection = activeSheet.Selection;
    selection.ForEach(function (range) {
        var value = range.GetValue();
        if (value &gt; 5) {
            range.SetFillColor(getColor());
        } 
    });

The full macro code is the following:

(function () 
{
    
           
    var uniqueColors = [Api.CreateColorFromRGB(255, 255, 0),
        Api.CreateColorFromRGB(204, 204, 255),
        Api.CreateColorFromRGB(0, 255, 0),
        Api.CreateColorFromRGB(0, 128, 128),
        Api.CreateColorFromRGB(192, 192, 192),
        Api.CreateColorFromRGB(255, 204, 0),
        Api.CreateColorFromRGB(255, 0, 0),
        Api.CreateColorFromRGB(0, 255, 255),
        Api.CreateColorFromRGB(255, 0, 255),
        Api.CreateColorFromRGB(218, 128, 128),
        Api.CreateColorFromRGB(128, 0, 0),
        Api.CreateColorFromRGB(128, 0, 128),
        Api.CreateColorFromRGB(0, 0, 128),
        Api.CreateColorFromRGB(165, 42, 42),
        Api.CreateColorFromRGB(220, 20, 60),
        Api.CreateColorFromRGB(255, 99, 71),
        Api.CreateColorFromRGB(205, 92, 92),
        Api.CreateColorFromRGB(240, 128, 128),
        Api.CreateColorFromRGB(255, 165, 0),
        Api.CreateColorFromRGB(255, 215, 0),
        Api.CreateColorFromRGB(184, 134, 11),
        Api.CreateColorFromRGB(218, 165, 32),
        Api.CreateColorFromRGB(173, 255, 47),
        Api.CreateColorFromRGB(0, 100, 0),
        Api.CreateColorFromRGB(50, 205, 50),
        Api.CreateColorFromRGB(70, 130, 80),
        Api.CreateColorFromRGB(75, 0, 130),
        Api.CreateColorFromRGB(72, 61, 139),
        Api.CreateColorFromRGB(255, 182, 193),
        Api.CreateColorFromRGB(188, 143, 143),
        Api.CreateColorFromRGB(112, 128, 144),
        Api.CreateColorFromRGB(210, 180, 140),
        Api.CreateColorFromRGB(105, 105, 105),
        Api.CreateColorFromRGB(216, 191, 216),
        Api.CreateColorFromRGB(240, 248, 255),
        Api.CreateColorFromRGB(102, 205, 170),
        Api.CreateColorFromRGB(255, 240, 245)];
    var uniqueColorIndex = 0;
        
        function getColor() {
            
            if (uniqueColorIndex === uniqueColors.length) {
                uniqueColorIndex = 0;
            }
            return uniqueColors[uniqueColorIndex++];
        }
    
    var activeSheet = Api.ActiveSheet;
    var selection = activeSheet.Selection;
    selection.ForEach(function (range) {
        var value = range.GetValue();
        if (value &gt; 5) {
            range.SetFillColor(getColor());
        } 
    });
    
})();

Now, let`s run our macro!

Highlight greater than values with ONLYOFFICE macro

And again, alternatively, you can use the SetFontColor method:

Highlight greater than values with ONLYOFFICE macro

This little macro is just one of the many possible implementations of our API methods. ONLYOFFICE macros are extremely versatile, and we welcome you to experiment and create your own scripts. Feel free to share your ideas or macros with us. We are always open to discussion and cooperation. Best of luck in your exploratory endeavors!