Worksheet_Change maximum events

[rickblunt]

Greetings, I have a worksheet that I use to record the filter efficiency in our cleanroom; This cleanroom has over 400 filters in it. In an effort to give the engineers a "graphical view" of the impact of the filters on the equipment in the room I created a worksheet where I use rectangles for each filter placed on the worksheet. (I tried doing this by sizing the cells, but I do not like merging cells when using VBA and the layout of the room does not allow me to align all of the filters in a grid pattern.) I then wanted to change the color of these shapes to red or green depending on the recorded values. Below is a snippet of code that I am using to achieve this, basically I enter a value in the table and the cell shape assigned to that cell changes colors -works great.

But my issue is that once I hit shape 210 the code stops working, If I delete one of the previous lines than 210 will work which leads me to believe that I have exceeded the maximum number of characters on this sheet. I was not aware that there was a maximum limit, so now I have a couple of questions I am hoping that you all could help me with:

1. Is there a maximum number or characters that can be used? I was not aware of it and I can't seem to find that answer on the web.
2. I thought about putting this or splitting this up into modules, but because I am using the "worksheet_change" event I believe that I have to use it in the worksheet, is that correct?
3. And probably more obvious of a question is maybe I should just condense the code in the first place. But I am not adept enough to know how to do this, my skill set is not good enough for that.

I appreciate any input that you have - thanks,

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
        End With
    End If


 If Target.Address = "$C$48" Then
    With ActiveSheet.Shapes("Rectangle 8").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
'and this gets repeated another 400+ times :/

 

[thisoldman]

I've been unable to find any documented limits for the number of shapes in the worksheet Shapes collection. With your repeated code, perhaps you've exceeded the code module size limits.

I'm a VBA dilettante, but this is shorter, no repeated code:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Column = 3 And Target.Row >= 46 Then
        With Shapes("Rectangle " & Target.Row - 45).Fill.ForeColor
        
            Select Case Target.Value2        ' Or Target.Value
                Case 0
                    .SchemeColor = 1         ' White
                Case Is >= 422
                    .SchemeColor = 50        ' Green
                Case Is >= 1
                    .SchemeColor = 10        ' Red
            End Select
            
        End With
    End If
End Sub

You may want to put an upper limit for the Target.Row in the If line:

If Target.Column = 3 And Target.Row >= 46 And Target.Row < 446 Then

 

[rickblunt]

Thanks for the code, I am still trying to get a handle on variables, I get the idea and it sure seems simple in theory, but every time I try to conjure one up I get it wrong :/

The code worked great, but it uncovered a problem that I will have to resolve. Through the process of building this thing and moving shapes around the shape numbers are not in the same sequence as the cells (rectangle1, rectangle2, rectangle232, rectangle10....) with my code it didn't matter because I just addressed each one. But I definitely like your code much better and it will be easier to add in filters when we do future expansion. I think that all I need to do is change the shape names and I should be fine.

I appreciate the assistance, thank you very much for your help

 

[thisoldman]

I wish you luck.

Learning programming with VBA is difficult, especially when you have little programming experience. So many objects, methods, properties, collections, ugly CamelCase, ...