.Range(.Cells ... vs .Range( Cells... vs "Microsoft Learn" page

Question

Surely the essence of my Question has come up before. What may be new about my Question is that it refers to what I hope is an error on this Microsoft.Learn page, and possibly my inane/deep questions. (Questions aren't deep, Answers might be.)

Actually, I've spent so much time on the code below that I'm sure the example linked to above is incorrect. Just an "ever-so-slight" oops. (Dot? No dot?)

But I'm not exactly sure why it's wrong and/or how how to interpret the "dot-versus-no-dot" appearance of .Range(Cells and .Range(.Cells in their contexts.

Specifically, what do the dots in .Cells actually do or refer to? If the answer is simply, "they cause offsets", okay, but other than executing the code and seeing that is the effect, how does this happen?

And what does the absence of dots before Cells do? Again, "the result is relative references" isn't what I'm after. But maybe those two answers are just how it is, so move on.

Maybe I haven't grasped the nuances of .Range(Cells vs. .Range(.Cells. Or maybe it's just Cells vs .Cells.

Exactly what does .Cells have to do with the With and Range("B2:Z100)?

I added to the example and included notes that I hope make sense: (Refer to P.S.)

    Sub cat()
      
          With Worksheets("Sheet1").Range("B2:E20")
'   Microsoft's code ...                                           
            .Range(.Cells(1, 1), .Cells(5, 3)).Font.Bold = True
            .Range(.Cells(1, 1), .Cells(5, 3)).Select
            MsgBox Selection.Address '             affects C3:E7 (not B2:D6)
    
'  My code ...     
            .Range(Cells(1, 1), Cells(5, 3)).Font.Color = vbRed
            .Range(Cells(1, 1), Cells(5, 3)).Select
            MsgBox Selection.Address '                      DOES affect B2:D6
            
          End With
        End Sub

P.S. I just looked at the last two examples in the link I provided. I think it might help me a lot to trace execution on paper of those two hunks of code and see if I can get the same outputs. It's not "simply" a Cells vs. .Cells deal, but a pretty deep dive into all of the above, plus an interesting experience with Item, which I thought I knew the meaning and usage of, but am now not sure, in the context provided.

Explanation (not required reading(!), but looking for improvements)

Beneath the With ...

.Range(.Cells does NOT involve relative references because of the dots before Cells.

Instead the arguments of Cells look like OFFSETs from B2. If they are...

.Range(.Cells(1,1)) says apply Offset(1,1) to B2, which gives C3 and

.Range(.Cells(5,3)) says apply Offset(5,3) to B2, which gives E7.

So Cells C3:E7 will be bold.

Further down ...

second .Range(Cells DOES contain RELATIVE REFERENCE to B2.

So Cells(1,1) represents 1st row and 1st col relative to B2, which is B2.

and Cells(5,3) represents 5th row and 3rd col relative to B2, which is D6.

So B2:D6 will be red.

Answer 1

You are correct. The example is wrong. But your conclusion (offset) also is not correct.

The culprit is Range.Range property (Excel) where at least property Cell1 is "The name of the range." and not a Cell object. And, if not named, the name of a cell is it's address.

The problem is that the cell address got from a cell object will always be related to the sheet. However, when used in Range.Range, it is interpreted in relation to the parent range. Thus Range.Range("A1:A1") is the range of cell in first row, first column in parent range. And Range.Range("C3:C3") is the range of cell in third row, third column in parent range.

Following example should show the problem:

Sub test()

 With ActiveSheet.Range("C3:Z100")
  Set o = .Cells(1, 1)
  MsgBox o.Address '$C$3
  Set o = .Range(.Cells(1, 1), .Cells(1, 1)) 'is .Range("C3:C3") - third row, third column in parent range
  MsgBox o.Address '$E$5
  
  Set o = Cells(1, 1) 'is Activesheet.Cells(1, 1)
  MsgBox o.Address '$A$1
  Set o = .Range(Cells(1, 1), Cells(1, 1)) 'is .Range("A1:A1") - first row, first column in parent range
  MsgBox o.Address '$C$3
  
 End With
End Sub

This shall show the problem. It is not the solution. The above only works when all is in ActiveSheet. Following will fail:

Sub test2()
 Set oWs = Worksheets("Data") ' Worksheet named "Data" is not the ActiveSheet
 With oWs.Range("C3:Z100")
  Set o = .Cells(1, 1)
  MsgBox o.Address(External:=True) '[WBName]Data!$C$3
  Set o = .Range(.Cells(1, 1), .Cells(1, 1)) 'is .Range("[WBName]Data!C3:C3") - third row, third column in parent range
  MsgBox o.Address(External:=True) '[WBName]Data!$E$5
  
  Set o = Cells(1, 1) 'is Activesheet.Cells(1, 1)
  MsgBox o.Address(External:=True) '[WBName]NameOfActiveSheet!$A$1
  Set o = .Range(Cells(1, 1), Cells(1, 1)) 'is .Range("NameOfActiveSheet!A1:A1") - Error 1004!
    
 End With
End Sub

The error is thrown because the range named NameOfActiveSheet!A1:A1 cannot be found in parent range Data!C3:Z100.

So correct usage not using ActiveSheet will be:

Sub test3()

 Set oWs = Worksheets("Data") ' Worksheet named "Data" is not the ActiveSheet
 With oWs.Range("C3:Z100")
  Set o = .Range(oWs.Cells(1, 1), oWs.Cells(1, 1)) 'is .Range("[WBName]Data!A1:A1") - first row, first column in parent range
  MsgBox o.Address(External:=True) '[WBName]Data!$C$3
  
  Set o = .Range(oWs.Cells(1, 1), oWs.Cells(5, 3))
  MsgBox o.Address(External:=True) '[WBName]Data!$C$3:$E$7
  o.Interior.Color = vbRed
  
 End With
End Sub

I know this is not really straight forward. And it looks to me like a kind of bug in Microsoft Excel VBA. But it only affects Range.Range, which gets a sub range out of a given range. Simplest solution would be to avoid this and always using Worksheet.Range.