detect more than one border color along single edge of range

[dlh]

I'm trying to write a macro to copy borders settings from one range to another. My trouble occurs when I have a source range with multiple settings contained in a single border. Example: A2 has a blue upper border, B2 has a green upper border. Look at the lower border of range A1:B1. In the border dialogue box, this would be displayed as a halftoned grey line. How do I detect this with VBA so that my macro can skip it?

[Bob Phillips]

Why do you need to know what it is just topy the format?

[dlh]

Copying the entire format doesn't solve the problem. There are elements of the format (i.e. merged cells) that I don't want to transfer.

[Bob Phillips]

Aah, more merged cells problems. Get rid of them, they are more trouble than they are worth.

[dlh]

That does seem to be a recurrent theme! I'm trying to create a macro tool for duplicating border settings of any chosen range that will work in all situations, whether or not there are merged cells. (My example doesn't involve merged cells.)

[Bob Phillips]

I am struggling to envisage the problem/data. Can you knock up a sample workbook that shows before and after?

[dlh]

Thanks for looking at this. I'm trying to write a subroutine DuplicateBorders that's intended as a tool that can be used in larger projects.

Here's a useage example. It's not meaningful by itself, but it demonstrates the desired functionality and the problem with ambiguous borders.

Refer to attatched screenshot of Sheet1.

Sub example()
Dim Source As Range, Target As Range
Set Source = Sheets("Sheet1").Range("B2:C2")
Set Target = Sheets("Sheet1").Range("B4")
Call DuplicateBorders(Source, Target)
End Sub


In this example, DuplicateBorders needs to recreate the border of a 2-cell source range in a 1-cell destination. The top border of the destination should become purple and the bottom border should not be changed.

I'm hoping there's a way to do this without checking every cell surrounding the source range.

[dlh]

Here's what I naively started with. This does not work in the situation above.


Sub DuplicateBorders(Source As Range, Target As Range)
Dim BorderTypes As New Collection
Dim BorderType As Variant

BorderTypes.Add xlDiagonalDown
BorderTypes.Add xlDiagonalUp
BorderTypes.Add xlEdgeBottom
BorderTypes.Add xlEdgeLeft
BorderTypes.Add xlEdgeRight
BorderTypes.Add xlEdgeTop
BorderTypes.Add xlInsideHorizontal
BorderTypes.Add xlInsideVertical

For Each BorderType In BorderTypes
With Target.Borders(BorderType)
.LineStyle = Source.Borders(BorderType).LineStyle
If .LineStyle <> xlLineStyleNone Then
.Color = Source.Borders(BorderType).Color
.Weight = Source.Borders(BorderType).Weight
End If
End With
Next BorderType

End Sub

[Bob Phillips]

How about this?



Sub DuplicateBorders(Source As Range, Target As Range)
Dim nRows As Long
Dim nCols As Long

nRows = Target.Rows.Count
nCols = Target.Columns.Count
If Source.Rows.Count < nRows Then nRows = Source.Rows.Count
If Source.Columns.Count < nCols Then nCols = Source.Columns.Count
Source.Cells(1, 1).Resize(nRows, nCols).Copy
Target.PasteSpecial Paste:=xlPasteFormats
End Sub

[dlh]

Thanks xld, but if the target has fewer columns than the source, your subroutine loses information about the right border of the source. And if the target has fewer rows than the source, the bottom border is missed.

[Bob Phillips]

That is hardly surprising, how would you know. Say the source has two cells both with a different colour right border, and the target was a single, which would you copy across?

[dlh]

Ah, I understand the confusion now. Thanks for sticking with me.

I want DuplicateBorders to preserve outside borders (outlines surrounding the range) and inside borders (gridlines inside a multi-cell range). This should mimic the functionality of Excel's border dialog box.

The subroutine should only copy border settings if the border is unambiguously defined in the source range, i.e., if it's not greyed out in the dialog box.