ps3cheats

I am using Excel VBA to store the Cell formatting properties of a single cell as string values.
I then can retrieve these values and format other cells.
I have one problem with the .Borders.ColorIndex property.
The code doesn't work on it and the message is "Invalid use of Null".
OI don't understand why this is null as the colors are blue and red.
I have marked the line in the code below with '***********
TIA




Dim SaveFormats(1 To 30) As String

Sub SaveFormatsAsStrings()
Dim CellRange As Range

Set CellRange = Selection

With CellRange.Cells(1)
With .Font
SaveFormats(1) = .Color
SaveFormats(2) = .ColorIndex
SaveFormats(3) = IIf(IsNull(.Background), "Null", .Background)
SaveFormats(4) = .Bold
SaveFormats(5) = .FontStyle
SaveFormats(6) = .Italic
SaveFormats(7) = .Name
SaveFormats(8) = .OutlineFont
SaveFormats(9) = .Shadow
SaveFormats(10) = .Size
SaveFormats(11) = .Strikethrough
SaveFormats(12) = .Subscript
SaveFormats(13) = .Superscript
SaveFormats(14) = .Underline
End With


With .Interior
SaveFormats(15) = .Color
SaveFormats(16) = .ColorIndex
SaveFormats(17) = .Pattern
SaveFormats(18) = .PatternColor
SaveFormats(19) = .PatternColorIndex
End With

With .Borders
SaveFormats(20) = .Color
SaveFormats(21) = .ColorIndex'***********
SaveFormats(22) = .LineStyle
SaveFormats(23) = .Value
SaveFormats(24) = .Weight
End With

SaveFormats(25) = .HorizontalAlignment
SaveFormats(26) = .VerticalAlignment
SaveFormats(27) = .NumberFormat
SaveFormats(28) = .WrapText
End With

End Sub


Sub GetFormatsFromStrings()
Dim CellRange As Range

Set CellRange = Selection

With CellRange.Cells(1)
With .Font
.Color = CLng(SaveFormats(1))
.ColorIndex = CLng(SaveFormats(2))
.Background = IIf(SaveFormats(3) = "Null", Null, SaveFormats(3))
.Bold = CBool(SaveFormats(4))
.FontStyle = SaveFormats(5)
.Italic = CBool(SaveFormats(6))
.Name = SaveFormats(7)
.OutlineFont = CBool(SaveFormats(8))
.Shadow = CBool(SaveFormats(9))
.Size = CLng(SaveFormats(10))
.Strikethrough = CBool(SaveFormats(11))
.Subscript = CBool(SaveFormats(12))
.Superscript = CBool(SaveFormats(13))
.Underline = CLng(SaveFormats(14))
End With

With .Interior
.Color = CLng(SaveFormats(15))
.ColorIndex = CLng(SaveFormats(16))
.Pattern = CLng(SaveFormats(17))
.PatternColor = CLng(SaveFormats(18))
.PatternColorIndex = CLng(SaveFormats(19))
End With

With .Borders
.Color = CLng(SaveFormats(20))
.ColorIndex = CLng(SaveFormats(21))
.LineStyle = CLng(SaveFormats(22))
.Value = CLng(SaveFormats(23))
.Weight = CLng(SaveFormats(24))
End With

.HorizontalAlignment = CLng(SaveFormats(25))
.VerticalAlignment = CLng(SaveFormats(26))
.NumberFormat = SaveFormats(27)
.WrapText = CBool(SaveFormats(28))

End With

End Sub

ps3cheats

You'll get this error when the borders are different. Borders returns a collection of borders (xlDiagonalDown, xlDiagonalUp, xlEdgeBottom, xlEdgeLeft, xlEdgeRight, or xlEdgeTop, xlInsideHorizontal, or xlInsideVertical), so you'll probably need to save each seperately....

With .Borders(xlEdgeBottom)
SaveFormats(20) = .Color
SaveFormats(21) = .ColorIndex'***********
SaveFormats(22) = .LineStyle
SaveFormats(23) = .Value
SaveFormats(24) = .Weight
End With

With .Borders(xlEdgeTop)
SaveFormats(20) = .Color
SaveFormats(21) = .ColorIndex'***********
SaveFormats(22) = .LineStyle
SaveFormats(23) = .Value
SaveFormats(24) = .Weight
End With

'etc