Iteration using multiple named ranges-CodePudding

What I'm trying to do is create a journaling spreadsheet that records the time and date of the entry at the time its submitted from a UserForm then updates the calendar on a "Splash" worksheet to change the cell interior and font colors to show that a journal entry has been created for that specific day.

I have a module created to iterate through what has already been imported from older journal entries from earlier this year and I want to change the interior color and text color of a cell in the named ranges named after the months. In the image below, the month names are not in the named ranges, just the list of numbers.

Calendar View

Basically, I want to search the dates, select and change the color of the cell of those dates in the calendar (see above). I can make it through the first month named range just fine but when it becomes a new month, it gives me run-time error 91.

Sub updateCells()
    Dim rCell As Range
    Dim rRng As Range: Set rRng = Worksheets("Journals").Range("A2:A44")
    Dim thisDate, thisMonth, thisDay
    Dim thisMonthRange As Range
    
    For Each rCell In rRng.Cells
        thisDate = Split(rCell.Text, " ")(0)
        thisMonth = MonthName(month(thisDate))
        thisDay = day(thisDate)
        Range(thisMonth).Find(what:=thisDay).Interior.ColorIndex = 10
        Range(thisMonth).Find(what:=thisDay).Font.Color = vbWhite
    Next rCell

I'm am relatively new to VBA so I don't understand what would be causing the run-time error.

CodePudding user response：

This takes having named ranges that are the actual names of the month, e.g., "January" list of dates (1-31) are referenced by Range("January"). I would use the following code to highlight the "18" in my Range("May") for today's date (2022-05-18):

Sub markCurrentDate()
    Cells.ClearFormats
    Dim currentMonth As String
    currentMonth = Format(Date, "mmmm")
    Dim currentDay As Long
    currentDay = Format(Date, "dd")
    Dim foundDate As Range
    Set foundDate = Range(currentMonth).Find(currentDay)
    foundDate.Interior.ColorIndex = 27
End Sub

Since we can't tell what your source cell for the date you're referring is, based on the current post, I used Date rather than a reference to a cell. The reference can be updated through, similar to being able to use With foundDate to add multiple format changes.

CodePudding user response：

I think the problem is likely to be that one of your named ranges does not cover the entire range of days. February surely doesn't, you're missing the 28th!

At any rate, as a consequence (and apparently only on the second turn (a Feb 28?)), you run into the Run-time error '91', because Range(thisMonth).Find(what:=thisDay) is resolving to Nothing instead of an expected Range object once you fail to find thisDay inside the named range.

Evidently, the code cannot execute Nothing.Interior.ColorIndex = 10.

If correct, your solution should be to double-check and fix the incorrect named ranges.

Incidentally, Range(thisMonth).Find(what:=thisDay) is also superfluous. For obvious reasons, each range simply starts at 1 and increments with 1. So we could simply use thisDay as the index. Instead of this:

        Range(thisMonth).Find(what:=thisDay).Interior.ColorIndex = 10
        Range(thisMonth).Find(what:=thisDay).Font.Color = vbWhite

Simply use this:

        With Range(thisMonth).Cells(thisDay)
            .Interior.ColorIndex = 10
            .Font.Color = vbWhite
        End With

Update: come to think of this, if you want to insist on using Range(thisMonth).Find(what:=thisDay), you should at the very least change the snippet to Range(thisMonth).Find(what:=thisDay, LookAt:=xlWhole).

Counterintuitively, Range.Find(...) seems to accept a partial match by default (xlPart) and it actually remembers the settings you used on your last find (in the same Excel 'session'). Also, it will not always start where you expect it to do (see further this documentation and this post: Using the .Find Function VBA - not returning the first value). E.g. a realistic error depending on your settings / active cell position might be that your code (and incidentally, also the code provided by Cyril) will change the formatting for a day 10, when in fact you were trying to change the formatting for a day 1.