VBA Out of Memory Error on Simple Code

Question

I have a very simple code I'm trying to run, here:

Sub Highlight()
Dim Diff As Range, cell As Range
Set Diff = Sheets(1).UsedRange.Columns("N:S")
For Each cell In Diff
If cells.Value2 > 0.1 Then
cell.Interior.ColorIndex = 0
End If
Next
End Sub

However, right at the If statement, I get runtime 7: Out of Memory. I'm running this on a fairly small data set (<5,000 cells), and have closed all other programs/ unnecessary processes, and task manager shows plenty of memory. I have no idea what could be causing this at this point.

Any ideas?

do you have any cells in Columns N:S that have text instead of numbers or errors? — Scott Craner
– Scott Craner, Commented Aug 25, 2016 at 1:17
Either you've got a typo (cells.Value2 instead of cell that you're using in the for each - you don't have a variable named cells declared anywhere) or you've got erroneous data in one or more cells. — Ken White
– Ken White, Commented Aug 25, 2016 at 1:23
I think @KenWhite has found the source of your error but on a related note, why not conditional formatting? — user4039065
– user4039065, Commented Aug 25, 2016 at 1:27
@KenWhite You were right on both counts; thanks for the help! — Errorum
– Errorum, Commented Aug 25, 2016 at 1:55
@Jeeped Because I want to run this from Powershell, macros are really easy to deal with in that environment — Errorum
– Errorum, Commented Aug 25, 2016 at 1:56

Ken White · Accepted Answer · 2016-08-25 02:17:32Z

3

You have a typo in your code. Your For Each variable is cell, but your code uses cells.Value2.

For Each cell In Diff                 ' Using cell (singular)
    If cells.Value2 > 0.1 Then        ' Using cells (plural)
        cell.Interior.ColorIndex = 0
    End If
Next

In addition, I suspect that you have values in your range (Diff) that aren't numeric or are empty.

answered Aug 25, 2016 at 2:17

Ken White

126k15 gold badges237 silver badges476 bronze badges

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

Collectives™ on Stack Overflow

VBA Out of Memory Error on Simple Code

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related