Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 Next End If End If End Sub -- Hugo |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Applying formatting to non-continuous named ranges
Try using Intersect. This will apply the format to the intersection
of the column you want with the named range: Change: Range(Cells(StartRow, Col),cells(EndRow,Col)).locked = False To: Intersect(Range("OutputRng"),Cells (StartRow,Col).EntireColumn).Locked = False Change: Range(Cells(StartRow, Col), Cells(EndRow, Col)).locked = True To: Intersect(Range("OutputRng"),Cells(StartRow, Col).EntireColumn).Locked = True |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Applying Named Ranges to Existing Formulas | Excel Discussion (Misc queries) | |||
Named range not continuous. How to display values? | Excel Worksheet Functions | |||
Clear Contents of multiple continuous ranges | Excel Programming | |||
Named ranges scope / workbook/worksheet level named ranges- changeswith variable use... | Excel Programming | |||
Conditional Formatting to HIghlight named ranges - error | Excel Programming |