limit to objects in ThisWorkbook?

[rickblunt]

Greetings, I have a workbook where I have used a bunch (like several hundred) of rectangles to graphically represent filtering devices in our facility that need to be measured on a regular basis. More or less a map of our facility that makes it easy for the techs to understand where they are and which filter is getting measured. When the measure the output of the filter, they enter that value into a cell in the worksheet, this value gets passed on through VBA to change the shape color to red or green or nothing. Here is a snippet of the code that I am using to change the color of the cell

Private Sub Worksheet_Change(ByVal Target As Range)    If Target.Address = "$C$46" Then
    '   Change autoshape color to red depending upon cell value, or blank of no value is entered.
        With ActiveSheet.Shapes("Rectangle 1").Fill.ForeColor
            If Target.Value = 0 Then
                .SchemeColor = 1
            ElseIf Target.Value >= 422 Then
                .SchemeColor = 50
            ElseIf Target.Value >= 1 Then
                .SchemeColor = 10
            Else
                'it must be less than 1
            End If
        End With
    End If
    If Target.Address = "$C$47" Then
    '   Change autoshape color to red depending upon cell value, or blank of no value is entered.
        With ActiveSheet.Shapes("Rectangle 2").Fill.ForeColor
            If Target.Value = 0 Then
                .SchemeColor = 1
            ElseIf Target.Value >= 422 Then
                .SchemeColor = 50
            ElseIf Target.Value >= 1 Then
                .SchemeColor = 10
            Else
                'it must be less than 1
            End If

This code gets repeated for each rectangle and it works very well. But the odd thing is that we have added 20 more filters to the plant and so when I went in to add the new devices only a couple of them are still working correctly. the first two changed color properly, but the last 18 didn't. The code all looks correct so I am wondering if there is a limit to the number of lines of code in the ThisWorkbook section or any module; The count currently stands at 2736.

I am sure many of you are looking at this code and thinking, "What in the world is that lunatic doing? there is a much easier way of doing that..." But I am not at all on the same level as a lot of you are, so this is the only way that I know of doing this.. I appreciate any input - thanks, Rick

 

[shg]

As to size, if you export the ThisWorkbook module, it should be less than 64kB.

If the limits are all the same, as in your example, then you can factor out the shape selection. Use a Case statement on the Address, and within each case, set the variable to the shape of interest. After the Case statement, change the color of the shape.

However, in the interest of separating code from data, I'd put the cell addresses and shape names in a table on a hidden sheet, and traverse that. That would reduce the code to a maybe a couple of dozen lines.