Macro Loop- Please help!

[arom]

For those who know VBA well, this will probably be an easy solve:

I would like the macro to loop through rows and highlight the cell in column G if the value is at least 2x greater than the value in the cell in column D.

Right now I have a very long macro... but I'd like to change it so goes through all the rows I assign it to instead of writing many lines of code (lots of if then statements):


Sub Macro4()
Range("G28").Select
If Range("G28") > 2 * Range("D28") Then
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
Range("G29").Select
If Range("G29") > 2 * Range("D29") Then
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
Range("G30").Select
If Range("G30") > 2 * Range("D30") Then
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If

etc.....

Any help is MUCH appreciated!!!! Thanks!!

-AR

 

[JSA715]

Sounds like you could just use conditional formatting...Have you tried it?

 

[arom]

Actually, yes- but the cells to be highlighted are in a pivot table and is often updated. There is a button (called 2x) that is assigned the macro to compare columns...

Thanks for the suggestion but I think it would be easiest if I could just change the code I have now to loop so I can always go back to the code and just change the rows to be compared (if I need to add rows). I just don't know enough VBA to write the do loop code...

Hope that makes sense!

Thanks!

-AR

 

[JSA715]

Well, what the largest number of rows you would expect to encounter?

100, 1000?

 

[Weaver]

Sub Macro4()
    Dim rng As Range
    For Each rng In Range("G28:G30")
        With rng
            If .Value > 2 * .Offset(-3, 0).Value Then
                With .Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .Color = 65535
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                End With
            End If
        End With
    Next rng
End Sub

Not tested (no data) but this should reproduce the code you posted.

Change the range in the for... statement to suit

 

[arom]

Thank you for your reponse!!! I'm almost there but I'm having a problem with the code... I think that it's not highlighting rows that have decimals, for example:

COL D COL G
10 20
5 15.5

It will highlight the first row but not the second... have any idea why?

 

[JSA715]

Not sure why, but mine errors on the .TintAndShade part.

If I take out the superfluous information and just worry about highlighting the cells then this worked for me using the numbers you provided:

Sub Macro4()
    Dim rng As Range
    For Each rng In Range("G28:G30")
        With rng
            If .Value > 2 * .Offset(-3, 0).Value Then
                .Interior.Color = 65535
            End If
        End With
    Next rng
End Sub

Its just a slightly simplified version of R8R's code.

 

[arom]

Thanks again but I am still getting an error. When I go to debug it highlights this row:

If .Value > 2 * .Offset(-3, 0).Value Then

and it says:

Application-define or object-defined error.

Any ideas?

Thanks!!

 

[Weaver]

Is this still a problem?

If so, could you reproduce the code as you are using it please?

I've tried this a few times, using formulas, strings etc in the cells and it still works fine.

The code is very simple and will fail if there are data errors in the ranges concerned, but it doesn't seem to give the error message you mentioned

 

[arom]

Yes, I am still having a problem... I thought it was because i'm sing the code on a pivot table- but even when I run the code on a sheet and put values in the corresponding cells, it doesn't work correctly... it is highlighting cells- but not the right ones. Here is the code:

Sub Macro11()
Dim rng As Range
For Each rng In Range("G18:G23")
With rng
If .Value > 2 * .Offset(-3, 0).Value Then
.Interior.Color = 65535
End If
End With
Next rng
End Sub

Am I doing something wrong??

Thanks for your help!