0

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.

Improve this question
7
  • 2
    Sorry but why would you not use CF? It's like wanting to put a nail into a wall but without using a hammer! Commented Feb 6, 2020 at 18:20
  • Your code is essentially three lines long, I wouldn't call that lengthy. Commented Feb 6, 2020 at 18:22
  • But also your code defines rng but then hardcodes everything. (Ok I get it, it's just an illustration.) Commented Feb 6, 2020 at 18:22
  • @SJR I actually posted a different topic with the exact question if I should use CF or not. I will do it in VBA, but I have to decide between CF and loops. The reason for using VBA is that the sheets I am working with are probably gonna change a few times, but yeah, I'm open to hear arguments pro or contra CF. Only downside I see so far is that CF doesn't allow you to manually remove the color in the cell. In any case, I also wondered how to best do it without CF just to learn about VBA. The answer by JvdV is not one I'm gonna use, but I will work through it to understand the code. Commented Feb 6, 2020 at 18:42
  • 1
    No, as a rule always use Excel's built-in functionality where possible. Commented Feb 6, 2020 at 18:58

1 Answer 1

Reset to default
2

"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).

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Ok...except for the obvious readability (and understandability) issue, why would you not recommend it? I'm not gonna use it for my problem, but I want to understand it.
@SJR, I knew this would yield diverse reactions. It's a perfect example where less code equals less quality. So yes: code can be shorter but this comes at the cost of flexibility and programmability. =).
@Alex, there are multiple reasons: no dynamic use of Ranges, risk of returning a larger string than Address can handle and more. I'm debating whether or not I should take it down. It's there to show: Yes, you can do it without a loop/less code, but no, this would be bad coding IMO
@JvdV If it were up to me, leave it up. You made clear not to use it, but it's fun to see what is and isn't possible. And it encourages me to look up JOIN.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.