Excel VBA Help
I am a beginner with VBA and am trying to continually learn.
I have a piece of code that is looking at Column B for a number and looking at column
E for an integer. The integer in column E will continually change every 15 seconds as I have a update links macro on an on time event running when the user clicks on a button thus creating a makeshift timer.
What the code is supposed to do as I understand it is change the fill color cell of a cell in column E when the conditions are met in both columns B and E.
The problem I am having is that code will only fire when the sheet is first opened or if a change is manually made to any part of the sheet. What I want it to do is fire whenever the integer in column E is changed change by the makeshift timer to meet the criteria written in the code.
Here is the code:
Private Sub Worksheet_Change(ByVal Target As Range) Dim i As Integer For i = 6 To 198 If Range("B" & i) = 5207 And Range("E" & i) > 6 Then Range("E" & i).Interior.ColorIndex = 3 ElseIf Range("B" & i) = 5207 And Range("E" & i) < 2.666 Then Range("E" & i).Interior.ColorIndex = 6 ElseIf Range("B" & i) = 5207 And Range("E" & i) > 2.666 Then Range("E" & i).Interior.ColorIndex = 4 ElseIf Range("B" & i) = 5215 And Range("E" & i) > 3 Then Range("E" & i).Interior.ColorIndex = 3 ElseIf Range("B" & i) = 5215 And Range("E" & i) < 0.75 Then Range("E" & i).Interior.ColorIndex = 6 ElseIf Range("B" & i) = 5215 And Range("E" & i) > 0.75 Then Range("E" & i).Interior.ColorIndex = 4 ElseIf Range("B" & i) = 5216 And Range("E" & i) > 3 Then Range("E" & i).Interior.ColorIndex = 3 ElseIf Range("B" & i) = 5216 And Range("E" & i) < 0.666 Then Range("E" & i).Interior.ColorIndex = 6 ElseIf Range("B" & i) = 5216 And Range("E" & i) > 0.666 Then Range("E" & i).Interior.ColorIndex = 4 ElseIf Range("B" & i) = 5222 And Range("E" & i) > 3 Then Range("E" & i).Interior.ColorIndex = 3 ElseIf Range("B" & i) = 5222 And Range("E" & i) < 0.666 Then Range("E" & i).Interior.ColorIndex = 6 ElseIf Range("B" & i) = 5222 And Range("E" & i) > 0.666 Then Range("E" & i).Interior.ColorIndex = 4 ElseIf Range("B" & i) = 5227 And Range("E" & i) > 4 Then Range("E" & i).Interior.ColorIndex = 3 ElseIf Range("B" & i) = 5227 And Range("E" & i) < 0.666 Then Range("E" & i).Interior.ColorIndex = 6 ElseIf Range("B" & i) = 5227 And Range("E" & i) > 0.666 Then Range("E" & i).Interior.ColorIndex = 4 ElseIf Range("B" & i) = 5228 And Range("E" & i) > 4 Then Range("E" & i).Interior.ColorIndex = 3 ElseIf Range("B" & i) = 5228 And Range("E" & i) < 0.666 Then Range("E" & i).Interior.ColorIndex = 6 ElseIf Range("B" & i) = 5228 And Range("E" & i) > 0.666 Then Range("E" & i).Interior.ColorIndex = 4 ElseIf Range("B" & i) = 5230 And Range("E" & i) > 7 Then Range("E" & i).Interior.ColorIndex = 3 ElseIf Range("B" & i) = 5230 And Range("E" & i) < 1.666 Then Range("E" & i).Interior.ColorIndex = 6 ElseIf Range("B" & i) = 5230 And Range("E" & i) > 1.666 Then Range("E" & i).Interior.ColorIndex = 4 ElseIf Range("B" & i) = 5231 And Range("E" & i) > 3 Then Range("E" & i).Interior.ColorIndex = 3 ElseIf Range("B" & i) = 5231 And Range("E" & i) < 0.75 Then Range("E" & i).Interior.ColorIndex = 6 ElseIf Range("B" & i) = 5231 And Range("E" & i) > 0.75 Then Range("E" & i).Interior.ColorIndex = 4 ElseIf Range("B" & i) = 5240 And Range("E" & i) > 3 Then Range("E" & i).Interior.ColorIndex = 3 ElseIf Range("B" & i) = 5240 And Range("E" & i) < 0.75 Then Range("E" & i).Interior.ColorIndex = 6 ElseIf Range("B" & i) = 5240 And Range("E" & i) > 0.75 Then Range("E" & i).Interior.ColorIndex = 4 ElseIf Range("B" & i) = 5241 And Range("E" & i) > 3 Then Range("E" & i).Interior.ColorIndex = 3 ElseIf Range("B" & i) = 5241 And Range("E" & i) < 0.666 Then Range("E" & i).Interior.ColorIndex = 6 ElseIf Range("B" & i) = 5241 And Range("E" & i) > 0.666 Then Range("E" & i).Interior.ColorIndex = 4 ElseIf Range("B" & i) = 5242 And Range("E" & i) > 7 Then Range("E" & i).Interior.ColorIndex = 3 ElseIf Range("B" & i) = 5242 And Range("E" & i) < 1.666 Then Range("E" & i).Interior.ColorIndex = 6 ElseIf Range("B" & i) = 5242 And Range("E" & i) > 1.666 Then Range("E" & i).Interior.ColorIndex = 4 ElseIf Range("B" & i) = 5243 And Range("E" & i) > 3 Then Range("E" & i).Interior.ColorIndex = 3 ElseIf Range("B" & i) = 5243 And Range("E" & i) < 0.666 Then Range("E" & i).Interior.ColorIndex = 6 ElseIf Range("B" & i) = 5243 And Range("E" & i) > 0.666 Then Range("E" & i).Interior.ColorIndex = 4 ElseIf Range("B" & i) = 5252 And Range("E" & i) > 3 Then Range("E" & i).Interior.ColorIndex = 3 ElseIf Range("B" & i) = 5252 And Range("E" & i) < 0.666 Then Range("E" & i).Interior.ColorIndex = 6 ElseIf Range("B" & i) = 5252 And Range("E" & i) > 0.666 Then Range("E" & i).Interior.ColorIndex = 4 ElseIf Range("B" & i) = 5258 And Range("E" & i) > 3 Then Range("E" & i).Interior.ColorIndex = 3 ElseIf Range("B" & i) = 5258 And Range("E" & i) < 0.75 Then Range("E" & i).Interior.ColorIndex = 6 ElseIf Range("B" & i) = 5258 And Range("E" & i) > 0.75 Then Range("E" & i).Interior.ColorIndex = 4 ElseIf Range("B" & i) = 5259 And Range("E" & i) > 3 Then Range("E" & i).Interior.ColorIndex = 3 ElseIf Range("B" & i) = 5259 And Range("E" & i) < 0.75 Then Range("E" & i).Interior.ColorIndex = 6 ElseIf Range("B" & i) = 5259 And Range("E" & i) > 0.75 Then Range("E" & i).Interior.ColorIndex = 4 ElseIf Range("B" & i) = 5260 And Range("E" & i) > 4 Then Range("E" & i).Interior.ColorIndex = 3 ElseIf Range("B" & i) = 5260 And Range("E" & i) < 0.666 Then Range("E" & i).Interior.ColorIndex = 6 ElseIf Range("B" & i) = 5260 And Range("E" & i) > 0.666 Then Range("E" & i).Interior.ColorIndex = 4 ElseIf Range("B" & i) = 5261 And Range("E" & i) > 4 Then Range("E" & i).Interior.ColorIndex = 3 ElseIf Range("B" & i) = 5261 And Range("E" & i) < 0.666 Then Range("E" & i).Interior.ColorIndex = 6 ElseIf Range("B" & i) = 5261 And Range("E" & i) > 0.666 Then Range("E" & i).Interior.ColorIndex = 4 ElseIf Range("B" & i) = 5263 And Range("E" & i) > 3 Then Range("E" & i).Interior.ColorIndex = 3 ElseIf Range("B" & i) = 5263 And Range("E" & i) < 0.666 Then Range("E" & i).Interior.ColorIndex = 6 ElseIf Range("B" & i) = 5263 And Range("E" & i) > 0.666 Then Range("E" & i).Interior.ColorIndex = 4 ElseIf Range("B" & i) = 5264 And Range("E" & i) > 3 Then Range("E" & i).Interior.ColorIndex = 3 ElseIf Range("B" & i) = 5264 And Range("E" & i) < 0.666 Then Range("E" & i).Interior.ColorIndex = 6 ElseIf Range("B" & i) = 5264 And Range("E" & i) > 0.666 Then Range("E" & i).Interior.ColorIndex = 4 ElseIf Range("B" & i) = 5285 And Range("E" & i) > 3 Then Range("E" & i).Interior.ColorIndex = 3 ElseIf Range("B" & i) = 5285 And Range("E" & i) < 0.75 Then Range("E" & i).Interior.ColorIndex = 6 ElseIf Range("B" & i) = 5285 And Range("E" & i) > 0.75 Then Range("E" & i).Interior.ColorIndex = 4 ElseIf Range("B" & i) = 6738 And Range("E" & i) > 1.0138 Then Range("E" & i).Interior.ColorIndex = 3 ElseIf Range("B" & i) = 6738 And Range("E" & i) < 1 Then Range("E" & i).Interior.ColorIndex = 6 ElseIf Range("B" & i) = 6738 And Range("E" & i) > 1 Then Range("E" & i).Interior.ColorIndex = 4 ElseIf Range("B" & i) = 6741 And Range("E" & i) > 5 Then Range("E" & i).Interior.ColorIndex = 3 ElseIf Range("B" & i) = 6741 And Range("E" & i) < 1 Then Range("E" & i).Interior.ColorIndex = 6 ElseIf Range("B" & i) = 6741 And Range("E" & i) > 1 Then Range("E" & i).Interior.ColorIndex = 4 ElseIf Range("B" & i) = 6922 And Range("E" & i) > 3 Then Range("E" & i).Interior.ColorIndex = 3 ElseIf Range("B" & i) = 6922 And Range("E" & i) < 0.666 Then Range("E" & i).Interior.ColorIndex = 6 ElseIf Range("B" & i) = 6922 And Range("E" & i) > 0.666 Then Range("E" & i).Interior.ColorIndex = 4 Else Range("E" & i).Interior.ColorIndex = 2 End If Next i If Target.Count > 1 Then Exit Sub Select Case Target.Column Case 6: Target.Offset(, 1) = Now Case Else: Exit Sub End Select End Sub
How about
Instead of putting it in the Worksheet.Change event, put it in a separate sub. Then add a line to call the sub every time your timer fires.