Excel Interop: unexplained result

[robertb_NZ]

I am writing a VB.NET application that processes an Excel spreadsheet. As each line is processed it is highlighted, and there is an option to step through the lines. I have a method to highlight the current line: -

Sub HighlightThisRow(Row As Integer)

' Current row is highlighted. Line above is un-highlighted, unless = DataRow
Dim Cell1 As String = Cell(Row, 1)
Dim Cell2 As String = Cell(Row, LastUsedColumn)
Dim xlRow As Excel.Range = xlWorkSheet.Range(Cell1, Cell2)
xlRow.Interior.Color = Highlight
xlRow.Borders.Color = Color.LightGray
If Row <> FirstDataRow Then

Cell1 = Cell(Row - 1, 1)
Cell2 = Cell(Row - 1, LastUsedColumn)
Dim xlRange As Excel.Range = xlWorkSheet.Range(Cell1, Cell2)
xlRange.Interior.Color = Normal​

End If​

End Sub​

Highlight and Normal are simply colors defined in global definitions: -

Dim Highlight As Color = Color.LightCyan
Dim Normal As Color = Color.White
​

This works perfectly, but why do I need the statement

xlRow.Borders.Color = Color.LightGray
​

Without this the statement

xlRow.Interior.Color = Highlight​

causes the cell borders to be list. However this is not the case with

xlRange.Interior.Color = Normal​

which leaves them unchanged.

My code is working, but I'm puzzled about this inconsistency. What don't I understand?

BTW moderator: what forum should we post VB Interop questions into? Is MS Office Automation considered a "Third Party"?

 

[JohnH]

causes the cell borders to be list

Not exactly sure what you mean by this, but this thread seems relevant: c# - How to change color of interior and not borders - Stack Overflow

Do the cells actually have a border or are you seeing the Gridlines?

In the absence of a fill color (e.g. white) the gridlines are shown.

 

[jmcilhinney]

Not exactly sure what you mean by this

I didn't either but, after your post, I realised that that was probably supposed to be "lost".

 

[robertb_NZ]

Thank you JohnH. The article you referred me to explained the inconsistency:

In the absence of a fill color (e.g. white) the gridlines are shown.​

So I've got caught up in the subtle differences between a Border and a Gridline, and whether Color.White is a color or the absence of a color. Although I would have expected that if White were the absence of a color you'd get the same results with Nothing, but actually you get black. Go figure!

Sorry about the typo: yes, I meant "lost", not "list", in "causes the cell borders to be list".

 

[jmcilhinney]

Nothing is numerically zero and white is (255,255,255) in RGB while black is (0,0,0), so it makes sense that Nothing corresponds to black. Visually though, white would be considered the absence of colour because white is the default fill.

 

[JohnH]

I don't have Office installed here at Windows 10, but in a previous Windows version I have customized windows background color and Excel would use this for cell color also (when not set otherwise). It is different from Color.White and handled differently, have a look here about using xlNone constant: How to use no fill for background color of a excel cell using c#? - Stack Overflow

 

[robertb_NZ]

Technically this makes sense, but it is an odd quirk that assigning any color EXCEPT WHITE to .interior.color causes the grid lines to be be lost. I guess the real issue is that I expected .Interior to be only the interior of the cell, not including the grid lines. Let's close this off, mark it as answered, I understand what is going on and I have a perfectly acceptable solution with

xlRow.Borders.Color = Color.LightGray​

Thank you JohnH and jmcilhinney for useful comments.