Explicit Range vs. Range variable?

[jvanbonn]

Will someone please tell me why these two codes are NOT equivalent?

Code 1 works fine, but does not allow for a dynamic range selection.

Sub CreateConditionalFormat(senior As String)

Sheets(senior).Cells.FormatConditions.Delete
  [COLOR=Blue]With[/COLOR] Sheets(senior).[COLOR=Red][B][B5:J46][/B][/COLOR].FormatConditions _
    .Add(xlExpression, xlExpression, "=$A5=True")
    [COLOR=Blue]With[/COLOR] .Font
      .Color = RGB(90, 90, 90)
      .Italic = [COLOR=Blue]True[/COLOR]
      .Strikethrough = [COLOR=Blue]True[/COLOR]
[COLOR=Blue]    End With
  End With
End Sub[/COLOR]

Code 2, I get an Object doesn't support this property or method run-time error.

Sub CreateConditionalFormat(senior As String)
'
' CreateStrikeGreyCondition Macro
'
[B][COLOR=Blue]Dim[/COLOR] [COLOR=Red]myRng [/COLOR][COLOR=Blue]As[/COLOR] [COLOR=Red]Range[/COLOR]

[COLOR=Blue]Set[/COLOR][/B] [COLOR=Red][B]myRng = Sheets(senior).[B5].CurrentRegion[/B][/COLOR]

Sheets(senior).[COLOR=Red][B]myRng[/B][/COLOR].FormatConditions.Delete
  [COLOR=Blue]With[/COLOR] Sheets(senior).myRng.FormatConditions _
    .Add(xlExpression, xlExpression, "=$A5=True")
    [COLOR=Blue]With[/COLOR] .Font
      .Color = RGB(90, 90, 90)
      .Italic = [COLOR=Blue]True[/COLOR]
      .Strikethrough = [COLOR=Blue]True[/COLOR]
[COLOR=Blue]    End With
  End With
End Sub[/COLOR]

As always, with deep respect & gratitude...
Thank you!

 

[wigi]

Leave out:

Sheets(senior).

in:

Sheets(senior).myRng.FormatConditions.Delete

in code 2 and it will work too.

 

[jvanbonn]

Thank you, thank you, thank you!

It seems like it's always the obvious that stumps me. The difficult stuff, I can usually figure out.

It makes complete sense why I should delete "Sheets(senior)." when I "Set myRng = Sheets(senior).[B5].CurrentRegion"

Thanks again!