Applying formatting to non-continuous named ranges



I am trying to get cells in a named range, call it: "OuputRng", to lock or
unlock depending on the date format of column headers. This is what I have
so far, where instead of using the named range "OutputRng" (A2:XFD7,
A9:XFD14), I have only been able to apply the locked/unlocked format to all
the cells (if I define EndRow = 14), but this includes row 8, which I don't
want to include in the named range "OutputRng". If I choose, on the other
hand to only apply this to rows 2 through 7, then I have not applied the
changes I would like to have applied to the rest of the named range. Does
anyone have thoughts on how to make it so only the rows in the named range
are targetted by the following code?

Sub DatesWithQuarters()
Dim X As Long, Col As Long, Row As Long, StartRow As Long, EndRow As Long
Dim StartDate As Variant, Duration As Variant
Col = 1
Row = 1
StartRow = 2
EndRow = 7
StartDate = InputBox("Tell me the starting month and 4-digit year")
Duration = InputBox("How many months should be listed?")
If IsDate(StartDate) And Len(Duration) > 0 And _
Not Duration Like "*[!0-9]*" Then
If Duration > 0 Then
StartDate = CDate(StartDate)
For X = 0 To Duration - 1
Cells(Row, Col).NumberFormat = "mmm-yyyy"
Cells(Row, Col).Value = DateAdd("m", X, StartDate)
Range(Cells(StartRow, Col),cells(EndRow,Col)).locked = False
If Month(DateAdd("m", X, StartDate)) Mod 3 = 0 Then
Col = Col + 1
Cells(Row, Col).NumberFormat = "@"
Cells(Row, Col).Value = Format(DateAdd("m", X, _
StartDate), "\Qq-yyyy")
Range(Cells(StartRow, Col), Cells(EndRow, Col)).locked = True
End If
Col = Col + 1
End If
End If
End Sub


Try using Intersect. This will apply the format to the intersection
of the column you want with the named range:
Range(Cells(StartRow, Col),cells(EndRow,Col)).locked = False
(StartRow,Col).EntireColumn).Locked = False
Range(Cells(StartRow, Col), Cells(EndRow, Col)).locked =
Col).EntireColumn).Locked = True

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question