Svgmassive
asked on
current region
Hello Svgmassive,
Strictly following your tamplate this code should work (I've also attached a spreadsheet with the code already implemented).
Please note that the address of the "colored range" selected will appear in the status bar to avoid a annoying message box.
Strictly following your tamplate this code should work (I've also attached a spreadsheet with the code already implemented).
Please note that the address of the "colored range" selected will appear in the status bar to avoid a annoying message box.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim nTop As Long
Dim nLeft As Long
Dim nBottom As Long
Dim nRight As Long
If Target.Interior.ColorIndex <> -4142 Then
While Target.Offset(nTop, nLeft).Interior.ColorIndex = Target.Interior.ColorIndex And Target.Column - nLeft > 1
nLeft = nLeft - 1
Wend
If Target.Column - nLeft > 1 Then
nLeft = nLeft + 1
End If
While Target.Offset(nTop, nLeft).Interior.ColorIndex = Target.Interior.ColorIndex And Target.Row - nTop > 1
nTop = nTop - 1
Wend
If Target.Row - nTop > 1 Then
nTop = nTop + 1
End If
While Target.Offset(nBottom, nRight).Interior.ColorIndex = Target.Interior.ColorIndex
nBottom = nBottom + 1
Wend
nBottom = nBottom - 1
While Target.Offset(nBottom, nRight).Interior.ColorIndex = Target.Interior.ColorIndex
nRight = nRight + 1
Wend
nRight = nRight - 1
Application.StatusBar = "Colored range selected: " & Me.Range(Target.Offset(nTop, nLeft), Target.Offset(nBottom, nRight)).Address
Else
Application.StatusBar = False
End If
End Sub
Book101.xlsm
ASKER
agneau your example uses the cell color but that only work if the cell has color,nutsch example looks at the last number in column f it should be actually be looking at the last date in column b and the and the last number in column f if there is a missing date eg.last date entry.Thanks
Hi Svgmassive,
I'm not sure if I understood your requirements.
In order to get the current region, Excel implements a native property of the range object called CurrentRegion
According to Excel documentation a "the current region is a range bounded by any combination of blank rows and blank columns."
Depending on the layout of your spreadhseet this would be the easiest way to code it. However your range examples contain blank columns (C, E, G and H) that prevent the CurrentRegion property to obtain the addresses you need.
I would suggest to redesign the spreadsheet layout to make use of CurrentRegion property, if this is not possible you need to clarify what is the logical organization of your spreadsheet:
Are columns C, E, G, H always empty?
How many "wekends"can exist in a single range?
How many "days" can exist in a single range?
Can more than one row of "emp id" column be empty? (or just one to limite the boundaries of the range)
Regards
I'm not sure if I understood your requirements.
In order to get the current region, Excel implements a native property of the range object called CurrentRegion
msgbox Cells(4,6).CurrentRegion.Address
According to Excel documentation a "the current region is a range bounded by any combination of blank rows and blank columns."
Depending on the layout of your spreadhseet this would be the easiest way to code it. However your range examples contain blank columns (C, E, G and H) that prevent the CurrentRegion property to obtain the addresses you need.
I would suggest to redesign the spreadsheet layout to make use of CurrentRegion property, if this is not possible you need to clarify what is the logical organization of your spreadsheet:
Are columns C, E, G, H always empty?
How many "wekends"can exist in a single range?
How many "days" can exist in a single range?
Can more than one row of "emp id" column be empty? (or just one to limite the boundaries of the range)
Regards
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window