Interior.ColorIndex of a Range

XLS Padlock
1

Dim ws As Worksheet
Set ws = Application.Worksheets(“test”)

Set r1 = ws.Range( ws.Cells(9, 2), ws.Cells(EndRow, 20))
Set r2 = ws.Range( ws.Cells(9, 22), ws.Cells(EndRow, 26))
'Set r3 = Union(r1, r2) - this do not work…?

r1.Interior.ColorIndex = xlNone - this is not possible??

2

Where is it not possible?

3

For the “Union” I get error when compiling.

r1.Interior.ColorIndex gives error when running the code. It just says there something wrong in the line when evaluating.
xlNone
xlAutomatic seems to be a problem.

"With …
End With "
is also not possible in the VBA compiler?

ws.Range(“B1”).HorizontalAlignment = xlLeft : "Unable to set HorizontalAlignment property of Range class when evaluating HorizontalAlignment "

4

I replaced the xlNone, xlAutomatic etc with the values on the link below, this solved my problem.

https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.constants.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1

1 Like
5

Yes, a lot of Excel constants are already defined in our VBA compiler but not all. Replacing them by their real values as you suggested works.