CODEWITHSUNDEEP
excelvba
Alex
Alex

Reputation: 535

VBA: Comparing single cells between entire rows

I have been working with conditional formatting and I thought how it would look like if replaced with a "manual" comparison in VBA.

Let's say I want to compare cells between Row 1 and Row 2, meaning I compare A1 to A2, B1 to B2 etc. Whenever the value in row 2 is bigger, I want to highlight this in row 2.

enter image description here If I don't want to do this with conditional formatting, how do I go about this? Do I have to loop through the cells to compare or is there a way to do it without a loop? With a loop it should look like this: 

Option Explicit

Sub Testing()
    Dim ws As Worksheet
    Dim i As Long
    Dim rng As Range

    Set ws = ThisWorkbook.ActiveSheet
    Set rng = ws.Range("A2:E2")

    For i = 1 To rng.Count
        If ws.Cells(2, i).Value > ws.Cells(1, i).Value Then
            ws.Cells(2, i).Interior.ColorIndex = 44
        End If
    Next
End Sub

Is there a way to use less code to achieve the same result? I'm just wondering if I'm unaware of some smarter, alternative way to go about this.

Upvotes: 0

Views: 68

Answers (1)

JvdV
JvdV

Reputation: 75840

"Is there a way to use less code to achieve the same result?"

Wouldn't recommend this for multiple reasons, but it can be done in a one-liner:

With Sheet1
    .Range(Join(Filter(.[IF(A2:E2>A1:E1,CHAR(COLUMN(A2:E2)+64)&2,"%")], "%", False), ",")).Interior.ColorIndex = 44
End With

Why does this work:

  • .[IF(A2:E2>A1:E1,CHAR(COLUMN(A2:E2)+64)&2,"%")] is short for the Application.Evaluate method. Anything between .[..] is basically a Worksheet.Function where VBA is smart enough to know I want to return an array of results. The . in front would make this refer to Sheet1. Resulting array > {"A2","B2","%","%","E2"}

  • Filter function (not really known) would take this array, and output an resulting array, filtering out the "%" values. Hence the FALSE parameter. Resulting array > {"A2","B2","E2"}

  • Then the Join function would concatenate this array into a string using a "," as delimiter. Rather basic, resulting in "A2,B2,E2"

  • This, on its turn, is a valid Range.Address we can use withing the Range(...) reference. Once we have our Range object we can set it's Interior property with the intended ColorIndex value.

Why would I not recommend this:

  • While readability may already be an issue, .[..] does not take variables, meaning a dynamic array will need to be written with the .Evaluate(..) method instead, including variables, extra quotes etc. adding to the issues with readability.

  • While VBA is smart enough to recognize we need an array returned, this may become sluggish on large Range object. A small one like in the example is no problem at all though.

  • Range address can have a maximum of 255 characters. Larger arrays mean larger strings, meaning a larger change this is going to return an error at some point.

There are ways to overcome the above, but really would be too much effort preventing the obvious > Use the build-in conditional formatting instead (or if you must, your current code is nice and clean too).

Upvotes: 2

Related Questions