What is an op-ed column?

Excel - cell a1 is a counter for cells in 'b' column filling with yellow

• QUESTION: Just tried your latest solution: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count > 1 Then Exit Sub If Target.Column = 12 Then ' column L On Error GoTo ErrHandler Select Case Target.Value Case 1 ' code to execute if a 1 is entered Case 2 Application.EnableEvents = False Target.Interior.ColorIndex = 28 Target.Font.ColorIndex = 28 Me.Cells(Target.Row, "G").Value = "Left Message With Broker To Contact Me" With Me.Cells(Target.Row, "I") .Value = Time() .NumberFormat = "hh:mm" .EntireColumn.AutoFit End With With Me.Cells(Target.Row, "C") .Value = "Yes" .Interior.ColorIndex = 40 End With End If End If Case 3 ' code to execute if a 3 is entered End Select ErrHandler: Application.EnableEvents = True End If End Sub ................. but keep getting: 'Compile error: End If without block If' with 'End If' highlighted in the prog ... when I enter either; 1 or 3 into column 'L' Rod Whitehouse. ---- ANSWER: Rod Whitehouse, That code really wasn't intended to be put in a module and executed. I was just trying to demonstrate how you would implement having code for multiple values. I just did a quick untested edit of the existing macro. Anyway, I have tested this and it worked for me if you want to see how it works by executing it: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count > 1 Then Exit Sub If Target.Column = 12 Then ' column L On Error GoTo ErrHandler Select Case Target.Value Case 1 ' code to execute if a 1 is entered MsgBox "You entered a 1" Case 2 Application.EnableEvents = False Target.Interior.ColorIndex = 28 Target.Font.ColorIndex = 28 Me.Cells(Target.Row, "G").Value = "Left Message With Broker To Contact Me" With Me.Cells(Target.Row, "I") .Value = Time() .NumberFormat = "hh:mm" .EntireColumn.AutoFit End With With Me.Cells(Target.Row, "C") .Value = "Yes" .Interior.ColorIndex = 40 End With Case 3 ' code to execute if a 3 is entered MsgBox "You entered a 3" End Select End If ErrHandler: Application.EnableEvents = True End Sub I put in a dummy statement to show a message box if you enter a 1 or a 3. You would replace that with whatever code you actually want to have run in those instances. ---------- FOLLOW-UP ---------- QUESTION: I tried your last solution and ¡­ after entering a ¡®3¡¯, I was getting the following message: ¡®You entered a 4¡¯ after entering a ¡®1¡¯, I was getting the following message: ¡®You entered a 2¡¯ ¡­¡­ however, I modified one of your previous solutions (see below) and the spreadsheet now inserts the Time() for both without any problems. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count > 1 Then Exit Sub If Target.Column = 12 Then ' column L If Target.Text = "3" Then 'On Error GoTo ErrHandler Application.EnableEvents = False Target.Interior.ColorIndex = 28 Target.Font.ColorIndex = 28 Me.Cells(Target.Row, "G").Value = "Left Message With Broker To Contact Me" Me.Cells(Target.Row, "K").Value = "Left Message With Broker To Contact Me" With Me.Cells(Target.Row, "I") .Value = Time() .NumberFormat = "hh:mm" .EntireColumn.AutoFit End With With Me.Cells(Target.Row, "C") .Value = "Yes" .Interior.ColorIndex = 40 End With With Me.Cells(Target.Row, "G") .Interior.ColorIndex = 28 End With End If End If ErrHandler: Application.EnableEvents = True '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' If Target.Count > 1 Then Exit Sub If Target.Column = 12 Then ' column L If Target.Text = "4" Then 'On Error GoTo ErrHandler Application.EnableEvents = False Target.Interior.ColorIndex = 15 Target.Font.ColorIndex = 1 Me.Cells(Target.Row, "G").Value = "No Action Required" Me.Cells(Target.Row, "L").Value = "Case Declined." With Me.Cells(Target.Row, "I") .Value = Time() .NumberFormat = "hh:mm" .EntireColumn.AutoFit End With With Me.Cells(Target.Row, "C") .Value = "N A" .Interior.ColorIndex = 15 .Borders.Item(xlEdgeRight).LineStyle = xlDash .Borders.Item(xlEdgeLeft).LineStyle = xlDash .Borders.Item(xlEdgeBottom).ColorIndex = 1 .Borders.Item(xlEdgeTop).ColorIndex = 1 .Borders.Item(xlEdgeRight).ColorIndex = 2 .Borders.Item(xlEdgeLeft).ColorIndex = 2 End With With Me.Cells(Target.Row, "G") .Interior.ColorIndex = 15 End With With Me.Cells(Target.Row, "F") .EntireColumn.AutoFit .Interior.ColorIndex = 15 End With With Me.Cells(Target.Row, "E") .EntireColumn.AutoFit .Interior.ColorIndex = 15 End With With Me.Cells(Target.Row, "D") .EntireColumn.AutoFit .Interior.ColorIndex = 15 End With End If End If 'ErrHandler: Application.EnableEvents = True End Sub Many thanks Rod Whitehouse. ----- ANSWER: Rod Whitehouse, the two lines in question MsgBox "You entered a 1" MsgBox "You entered a 3" but you say the first puts up the message You entered a 2 and the second message says You entered a 4 as you can see there are no variables in the strings - so if you get such messages there is no way I can explain that. The code worked fine for me. I can't say what happends to it after I post it. ---------- FOLLOW-UP ---------- QUESTION: Thankyou for the answer - it may be some kind of technical set up with my system again, I'm not sure. Could you possibly set up the following in an excell spreadsheet: If any cell in the 'B' column fills with yellow, I need cell A1 to act as a counter and count the number of cells being filled with yellow. Rod Whitehouse. ---

• Answer:

  Rod Whitehouse, You can add this in at the bottom of your existing change event code Dim rr as Range Dim yellowcnt as Long set rr = Intersect(Me.UsedRange,me.columns(2)).Cells yellowcnt = 0 for each cell in rr if cell.interior.ColorIndex = 6 then yellowcnt = yellowcnt + 1 next Application.EnableEvents = False me.Range("A1").Value = yellowcnt Application.EnableEvents = True I have put in EnableEvents code to avoid a recursive call - but you would have to coordinate that with your existing code. Here is what a minimal change event would look like (tested and worked for me) Private Sub Worksheet_Change(ByVal Target As Range) Dim rr As Range Dim yellowcnt As Long If Target.Count > 1 Then Exit Sub If Target.Column <> 12 Then Exit Sub Set rr = Intersect(Me.UsedRange, Me.Columns(2)).Cells yellowcnt = 0 For Each cell In rr If cell.Interior.ColorIndex = 6 Then yellowcnt = yellowcnt + 1 Next Application.EnableEvents = False Me.Range("A1").Value = yellowcnt Application.EnableEvents = True End Sub But I know you already have a change event - so the code would need to be included in that as you can only have one change event in a sheet module.