Cool.
To use this in your own workbook, what you need to do is this.
- Open your workbook in Excel
- Find sheet that has your L19 & L20 ranges on it
- Right click the sheet's tab and choose "View Code"
- Paste the following in the window that opens up:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sRows() As String
Dim lRow As Long
Dim sCols() As String
Dim lCol As Long
Dim wsTarget
Select Case Target.Address
Case Is = "$L$19", "$L$20"
Debug.Print Target.Address
'Set target worksheet to mark up here
Set wsTarget = ThisWorkbook.Worksheets("Sheet2")
'Get array of values
sCols = Split(ActiveSheet.Range("$L$19"), ",")
sRows = Split(ActiveSheet.Range("$L$20"), ",")
'Ignore errors (ignores invalid characters)
On Error Resume Next
'Mark worksheet
With wsTarget
For lRow = LBound(sRows) To UBound(sRows)
For lCol = LBound(sCols) To UBound(sCols)
.Range(sCols(lCol) & sRows(lRow)).Value = 1
Next lCol
Next lRow
End With
Case Else
'Do nothing
End Select
End Sub
- (If you see a line that says Option Explicit, then put it under that line.)
- Adjust the following line to refer to the worksheet you want to paste your stuff to. (Just change the name of the worksheet between the quotes.)
Code:
Set wsTarget = ThisWorkbook.Worksheets("Sheet2")
- Go back to the Excel window and test it.
- Save the file once you are happy it's working
If you are using Excel 2007/2010, you will need to save the file in an xlsm file format, as xlsx cannot contain macros.
HTH,
Bookmarks