Stepping through a for routine

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Stepping through a for routine

Post by bknight »

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cel As Range
    Dim rng As Range
    Dim dteToday As Date
    Dim dteTargetDate As Date
    Set rng = Intersect(Range("D2:D" & Rows.Count & ",J2:J" & _
        Rows.Count & ",O2:O" & Rows.Count), Target)
    If Not rng Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        rng.Font.ColorIndex = xlColorIndexAutomatic
        rng.Interior.ColorIndex = xlColorIndexNone
        dteToday = Range("F1").Value
        For Each cel In rng
            If IsDate(cel.Value) Then
                dteTargetDate = cel.Value
                    If dteTargetDate Then
                        If dteTargetDate < dteToday Then
                            cel.Font.ColorIndex = 4 ''Green
                        Else
                            cel.Font.ColorIndex = 0
                        End If
                    If dteTargetDate + 75 <= dteToday Then
                        cel.Interior.ColorIndex = 6 ''Yellow
                    Else
                        cel.Interior.ColorIndex = 0 ''White
                    End If
                    End If
            Else
                EntryError = MsgBox("Incorrect Date Entry", 0)
            End If
        Next cel
    End If
    Cells(2, 1).Select
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Cells(2, 1).Activate
End Sub
In the above code, which Hans helped me so much. The spreadsheet had a different appearance today and I debugged the code and ran up to some questions. The code worked in the past but "seemed" to malfunction today.
1. cel is dimensioned but doesn't appear to be set anywhere.
2. For Each cel In rng in this statement this seemed to me to address each cel (not set yet) but in reality it only addresses the changed cell.
What am I missing? Is there a better way to write this?

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Stepping through a for routine

Post by HansV »

The lines

Code: Select all

    Set rng = Intersect(Range("D2:D" & Rows.Count & ",J2:J" & _
        Rows.Count & ",O2:O" & Rows.Count), Target)
sets the variable rng to those cells in columns D, J and O that have been changed. That is usually just one cell, but it could be multiple cells if you - for example - select some cells in column D or J or O and clear them by pressing Delete.
The loop

Code: Select all

        For Each cel In rng
            ...
        Next cel
loops through the cells of rng. In other words, cel is set to each of the cells in rng in turn. Again, that's usually just one cell, but there could be more.

What was the problem? Did you get an error message, and if so, what did it say?
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Stepping through a for routine

Post by bknight »

The worksheet had no color even with a change on the sheet. I went back over and copied multiple cells and the function did work, colorizing as before.

Even a change did not produce a color, first issues, but the multiple paste special "formats" worked.

User avatar
HansV
Administrator
Posts: 78229
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Stepping through a for routine

Post by HansV »

The code should work OK, unless you have conditional formatting on columns D/J/O. Conditional formatting always overrules formatting that you set manually or using VBA.
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Stepping through a for routine

Post by bknight »

I believe it will again, but it was a shock to see it all white and black.

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: Stepping through a for routine

Post by bknight »

Ah I've discovered he problem. There was a workbook open macro that was missing. I don't know how that happened; but a backup cleared that issue.