Using Null (or something else) to avoid 'Out of Memory'

Question

I have this snippet of code that will highlight select rows of data within a table different colors based on varying parameters:

Public Sub HighlightRecentSampleRequests()

Dim sht As Worksheet
Dim LastRow As Long
Dim cell As Range
Dim dt, txt

Set sht = Worksheets("Sample Transfer Log")
LastRow = sht.Cells(Rows.Count, "A").End(xlUp).Row

For Each cell In sht.Range("K3:K" & LastRow).Cells
    dt = cell.Value
    txt = cell.Offset(0, -3).Value
If dt >= Date - 7 And txt = "Sample Receipt" Then
    cell.Range("A1:P1").Offset(0, -10).Interior.ColorIndex = 45 'orange
ElseIf dt >= Date Then
    cell.Range("A1:P1").Offset(0, -10).Interior.ColorIndex = 6 'yellow
Else
    cell.Range("A1:P1").Offset(0, -10).Interior.Color = RGB(220, 230, 242) 'default color
End If
Next

End Sub

I made an alteration to my original code to produce the above to make it do what I wanted. Now I am receiving the 'Out of Memory' message constantly when I run my system. This seems to be due to the last portion of the If statement where all cells that do not meet the previous criteria will be highlighted as the default color. I attempted to refer to the range using a variable and then setting that variable to 'Nothing' after completing its use, but that returned a message of 'Invalid Use of Null' and thereafter those variables were useless and needed to be deleted for the code to work again.

Basically, I would like to get rid of the 'Out of Memory' message without screwing up my entire code.

Any reason for not using conditional formatting instead of vba? — DragonSamu, Sep 08 '15 at 16:35
Not sure if range.interior.color will take hex but you could try. http://dmcritchie.mvps.org/excel/colors.htm — MatthewD, Sep 08 '15 at 16:39
How about not setting it to any color. You are setting it to the default for what reason? Is it not already the default? — MatthewD, Sep 08 '15 at 16:40
@MatthewD and @ DragonSamu I am using this as part of a system where users input values into several fields within a Userform, and once submitted, those values are logged into a table. I was not sure whether conditional formatting was robust enough to cover this for all possible cells within the table. Alternatively, I wanted to see if this was possible in VBA. Also, this is a problem that has come up before and I'd like to know a general solution that I can apply elsewhere. — Karl Gilbert, Sep 08 '15 at 16:40
You can use conditional formatting and put it on the full range based on the criteria — DragonSamu, Sep 08 '15 at 16:43
@MatthewD The default color noted in the script is not the default color for excel, but a light blue. I need to return past inputs of the table to the default color once the date criteria is no longer matched. — Karl Gilbert, Sep 08 '15 at 16:44
Can you use cell.Range("A1:P1").Offset(0, -10).Interior.ColorIndex = 34 Maybe the RGB() is causing the issue. — MatthewD, Sep 08 '15 at 16:49
See some info about setting the .color value in the second answer here. http://stackoverflow.com/questions/1426369/excel-set-background-colour-of-cell-to-rgb-value-of-data-in-cell — MatthewD, Sep 08 '15 at 17:01
@MatthewD I do not believe that the color is the issue, I think that it is because the code for setting the default color, after the other If conditions have been checked, runs through literally every cell within that column range. — Karl Gilbert, Sep 08 '15 at 17:03
@MatthewD To be fair, I checked to see if using a color index would reduce the amount of memory being used and it does not, or if it does it is not enough to keep the 'Out of Memory' error from appearing. — Karl Gilbert, Sep 08 '15 at 17:10
Try select case statements. Put the most likely one at the top. — MatthewD, Sep 08 '15 at 17:13
@KarlGilbert are you interested in the Conditional formatting solution? — DragonSamu, Sep 08 '15 at 17:44
@ScottCraner I tried that, but unfortunately that did not work either. — Karl Gilbert, Sep 08 '15 at 17:56
@DragonSamu I know now that I can implement the Conditional formatting solution, but I'd really like to have a VBA solution to fix the 'Out of Memory' problem. — Karl Gilbert, Sep 08 '15 at 17:57
@KarlGilbert ok, how much rows are we generally talking about? or if possible can you send me the file for testing — DragonSamu, Sep 08 '15 at 17:58
@DragonSamu Only 4 rows can be added at any one time. I can send you the file, what is the best way to go about that (I'm new to this site) ? — Karl Gilbert, Sep 08 '15 at 18:11
@KarlGilbert upload it to [wetransfer.com](http://www.wetransfer.com) — DragonSamu, Sep 08 '15 at 18:17
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/89096/discussion-between-dragonsamu-and-karl-gilbert). — DragonSamu, Sep 08 '15 at 18:28

score 2 · Accepted Answer · answered Sep 08 '15 at 20:08

After looking at the actual file besides your code processing a Range which varies and is being called by an UserForm it is not the direct cause of the memory issues.

Because there are multiple UserForms which are being loaded into memory when .Show is used (for the specific UserForm). They do not get released from memory by using .Unload after the UserForm is processed but stay active in the background and taking up memory space.

By properly using .Unload after having processed an UserForm and .Load or .Show the UserForm when needed again memory is freed up properly.

score 0 · Answer 2 · answered Sep 08 '15 at 17:17

You could try using case statements. Something like this.

    Select Case dt
        Case Is >= Date - 7
            If txt = "Sample Receipt" then
                cell.Range("A1:P1").Offset(0, -10).Interior.ColorIndex = 45 'orange
            End If
        Case Is >= Date
            cell.Range("A1:P1").Offset(0, -10).Interior.ColorIndex = 6 'yellow
        Case Else
            cell.Range("A1:P1").Offset(0, -10).Interior.Color = RGB(220, 230, 242) 'default color
    End Select

Using Null (or something else) to avoid 'Out of Memory'

2 Answers2