If you don't have access to the UNIQUE function, you can use a PivotTable to generate the desired result. There are a few idiosyncrasies with PivotTables but it'll get the job done.
1. Create the PivotTable
Click anywhere in your data and, on the Insert ribbon click PivotTable
Make sure the range is your entire table and click OK. The range may be wrong if ...
If ActiveCell.Offset(0, -2).Value = "W1" Or ActiveCell.Offset(0, -2).Value = "OUTSIDE"
Haven't done VBA in forever and do not currently have it but:
ActiveCell.Offset(0, -2).Value = "W1" Or "OUTSIDE"
does not look correct to me, as '=' has higher precendence than the logical operator 'Or', so this is doing :
When you are using a formula IF(H1=FALSE,[VALUE IF TRUE],[VALUE IF FALSE]) the formula checks for a FALSE or a blank cell in H1.
If you wish to retain your current requirements, put the if blank at the beginning.
The "ERROR" bit ...
This sets vertical borders for all cells in the usedrange after row 1.
'Sets vertical borders
Dim iRange As Range
Dim iCells As Range
Set iRange = ActiveWorkbook.Worksheets("Sheet1").UsedRange.Offset(1, 1)
For Each iCells In iRange
If Not IsEmpty(iCells) Then
.LineStyle = xlContinuous
Doing this with an array formula with Excel 2016 will be quite involved.
Why not use an easier approach that doesn't involve complicated formulas or VBA?
Create a list of dates from the start date to the end date. In the next column, use a Match formula to find the date in your data. You could use a formula that returns a TRUE/FALSE result, like
If you are OK with a thin black border, then conditional formatting will work. If you really want a thick black border, you will need VBA event code, since you cannot modify the thickness of a border in the conditional formatting border property.
To enter this event-triggered Macro, right click on the sheet tab.
Select "View Code" from the right-...
This is easily accomplished with conditional formatting.
Make the active cell J2
Open up the conditional format dialog box
Select Use Formula for Conditional Formatting
Type in the formula =$J2<>$J1 and set the border as you like
Adjust the Applies To cells to make the underline go to the cells you want
What you describe, literally, in your numbered example set of actions is how Excel has always worked.
When you copy a CELL, whole, Excel will only paste it whole, not inside a cell as part of that cell's contents. Excel has ALWAYS required the "cumbersome" workaround you allude to in order to paste into a cell's contents vs. pasting a whole cell ...