excel vba compare one range with another

Question

I'm quite massively out of my depth here having never used vba before (My main role is primarily sql based), i'd like to sit down and spend a few days actually learing how all this works but i don't have days right now, hence throwing myself on your mercy!

Sub updateduration() If Worksheets("Sheet1").Range("I4").Value = "Y" Then Worksheets("Sheet1").Range("H4").Interior.ColorIndex = 43 Else If Worksheets("Sheet1").Range("J4").Value = 1 Then Worksheets("Sheet1").Range("H4").Interior.ColorIndex = 3 Else If Worksheets("Sheet1").Range("J4").Value = 0 Then Worksheets("Sheet1").Range("H4").Interior.ColorIndex = 45 End If End If End If End Sub

As ugly as that probably is it works, Im now trying to adapt it so it adjusts all the cells in range H4:H34 one by one checking each if statement against the equivelant cell (i.e. range I4:I34 and range J4:34)

I've been looking at 'for each' to form an initial loop but am struggling to figure out how to specify which cells in the other ranges to look at in each iteration of the loop.

Any help or advice appreciated

L

Is the range "fixed" ? or is it being selected by the user ? or passed in by another process ? — Ditto
– Ditto, Commented Jul 24, 2015 at 14:20

Eddy · Accepted Answer · 2015-07-24 14:25:11Z

Welcome to SE, L! You're off to a good start, and you're right that a FOR..NEXT loop is what you're looking for. The trick is to edit your Range with a variable, like this:

Sub updateduration() Dim startRow As Integer, endRow As Integer startRow = 4 'first row to compare/update endRow = 34 'last row to compare/update For myRow = startRow To endRow If Worksheets("Sheet1").Range("I" & myRow).Value = "Y" Then Worksheets("Sheet1").Range("H" & myRow).Interior.ColorIndex = 43 Else If Worksheets("Sheet1").Range("J" & myRow).Value = 1 Then Worksheets("Sheet1").Range("H" & myRow).Interior.ColorIndex = 3 Else If Worksheets("Sheet1").Range("J" & myRow).Value = 0 Then Worksheets("Sheet1").Range("H" & myRow).Interior.ColorIndex = 45 End If End If End If Next myRow End Sub

Excellant stuff, you have provided the missing link in my understanding - 'Range("J" & myRow)'. I hadnt understood it was that simple to combine text & a variable within a range declaration, Id been trying to form variable that updated itself in each iteration but was getting befuddled in multiloop hell. Many thanks for such a quick response, saved my bacon!

Collectives™ on Stack Overflow

excel vba compare one range with another

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related