Range Cell Address Reference Completed

Perhaps these procedures especially Get_CellCollection() will help in the meantime? There is a simple Forms application for testing. I think I tested most of the combinations of cell range references ... seems OK.

Imports System
Imports System.Collections.Generic
Imports System.Text
Imports GemBox.Spreadsheet
Imports GemBox.Spreadsheet.ConditionalFormatting
Imports GemBox.Spreadsheet.PivotTables

Class Form1

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

' If using Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

Dim ef As ExcelFile = New ExcelFile
Dim ws As ExcelWorksheet = ef.Worksheets.Add("Range Formatting")
Dim range As CellRange = Nothing
Dim rowCount As Integer = 20

' Specify data formatting.

ws.Columns(0).SetWidth(3, LengthUnit.Centimeter)
ws.Columns(1).SetWidth(3, LengthUnit.Centimeter)
ws.Columns(2).SetWidth(3, LengthUnit.Centimeter)
ws.Columns(3).SetWidth(3, LengthUnit.Centimeter)
ws.Columns(3).Style.NumberFormat = "[$$-409]#,##0.00"
ws.Columns(4).SetWidth(3, LengthUnit.Centimeter)
ws.Columns(4).Style.NumberFormat = "yyyy-mm-dd"
ws.Columns(5).SetWidth(3, LengthUnit.Centimeter)

Dim cells = ws.Cells

' Specify header row.
cells(0, 0).Value = "Departments"
cells(0, 1).Value = "Names"
cells(0, 2).Value = "Years of Service"
cells(0, 3).Value = "Salaries"
cells(0, 4).Value = "Deadlines"
cells(0, 5).Value = "Comments"

' Insert random data to sheet.
Dim random = New Random()
Dim departments = New String() {"Legal", "Marketing", "Finance", "Planning", "Purchasing"}
Dim names = New String() {"John Doe", "Fred Nurk", "Hans Meier", "Ivan Horvat"}
Dim comments = New String() {"", "", "Overdue", ""}
For i As Integer = 0 To rowCount - 1
cells(i + 1, 0).Value = departments(random.Next(departments.Length))
cells(i + 1, 1).Value = names(random.Next(names.Length)) + " "c + (i + 1).ToString()
cells(i + 1, 2).Value = random.Next(1, 31)
cells(i + 1, 3).Value = random.Next(10, 101) * 100
cells(i + 1, 4).Value = DateTime.Now.AddDays(random.Next(-1, 2))
cells(i + 1, 5).Value = comments(random.Next(comments.Length))
Next

Get_CellCollection(ws, "D16:F21", range)
range.Style.FillPattern.SetSolid(Color.Yellow)

Get_CellCollection(ws, "7:8", range)
range.Style.Font.Weight = ExcelFont.BoldWeight
range.Style.FillPattern.SetSolid(Color.LightGreen)

Get_CellCollection(ws, "C4", range)
range.Style.FillPattern.SetSolid(Color.LightBlue)

Get_CellCollection(ws, "E", range)
range.Style.FillPattern.SetSolid(Color.LightSlateGray)

Get_CellCollection(ws, "12", range)
range.Style.FillPattern.SetSolid(Color.MistyRose)

Get_CellCollection(ws, "A17:B", range)
range.Style.FillPattern.SetSolid(Color.OrangeRed)

System.IO.File.Delete(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) & "\Range Formatting.xlsx")
ef.Save(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) & "\Range Formatting.xlsx")

Me.Close()

End Sub

Private Function Get_CellCollection(ws As GemBox.Spreadsheet.ExcelWorksheet, CellRef As String, _
ByRef MyCellRange As GemBox.Spreadsheet.CellRange) As Boolean

' Fetches a CellRange from the Worksheet using Excel-type CellRef string.
' Any "$" absolute references are allowed and stripped out.

Dim CellRef1 As String = ""
Dim CellRef2 As String = ""
Dim DelimIdx As Integer
Dim Row1 As Integer = 0
Dim Row2 As Integer = 0
Dim Col1 As Integer = 0
Dim Col2 As Integer = 0
Dim Syntax1 As Integer = 0
Dim Syntax2 As Integer = 0
' 0 = invalid syntax
' 1 = whole column selection syntax like "AD" - col is valid, row is not modified
' 2 = whole row selection syntax like "3" - row is valid, col is not modified
' 3 = cell reference like "BC3" - both row and col are valid

CellRef = Replace(CellRef, "$", "") ' remove any Excel "$" absolute references since these are unsupported in GB
DelimIdx = InStr(CellRef, ":")
If DelimIdx > 0 Then ' cellrange with two tokens CellRef1 and CellRef2

' Process the first token, CellRef1
CellRef1 = Mid(CellRef, 1, DelimIdx - 1)
If Not Parse_ExcelRefToken(CellRef1, Syntax1, Row1, Col1) Then Return False

' Process the second token, CellRef2
CellRef2 = Mid(CellRef, DelimIdx + 1, Len(CellRef) - DelimIdx)
If Not Parse_ExcelRefToken(CellRef2, Syntax2, Row2, Col2) Then Return False

Else ' single row or column specified by one token CellRef
If Not Parse_ExcelRefToken(CellRef, Syntax1, Row1, Col1) Then Return False
End If

Select Case Syntax1
Case 1
Select Case Syntax2
Case 0
MyCellRange = ws.Cells.GetSubrangeAbsolute(0, Col1, ws.Rows.Count - 1, Col1)
Case 1
MyCellRange = ws.Cells.GetSubrangeAbsolute(0, Col1, ws.Rows.Count - 1, Col2)
Case 2
MyCellRange = ws.Cells.GetSubrangeAbsolute(Row2, Col1, Row2, Col1)
Case 3
MyCellRange = ws.Cells.GetSubrangeAbsolute(0, Col1, Row2, Col2)
End Select
Case 2
Select Case Syntax2
Case 0
MyCellRange = ws.Cells.GetSubrangeAbsolute(Row1, 0, Row1, ws.Columns.Count - 1)
Case 1
MyCellRange = ws.Cells.GetSubrangeAbsolute(Row1, Col2, Row1, Col2)
Case 2
MyCellRange = ws.Cells.GetSubrangeAbsolute(Row1, 0, Row2, ws.Columns.Count - 1)
Case 3
MyCellRange = ws.Cells.GetSubrangeAbsolute(Row1, 0, Row2, Col2)
End Select
Case 3
Select Case Syntax2
Case 0
MyCellRange = ws.Cells.GetSubrangeAbsolute(Row1, Col1, Row1, Col1)
Case 1
MyCellRange = ws.Cells.GetSubrangeAbsolute(Row1, Col1, ws.Rows.Count - 1, Col2)
Case 2
MyCellRange = ws.Cells.GetSubrangeAbsolute(Row1, Col1, Row2, ws.CalculateMaxUsedColumns - 1)
Case 3
MyCellRange = ws.Cells.GetSubrangeAbsolute(Row1, Col1, Row2, Col2)
End Select
End Select
Return True

End Function

Private Function Parse_ExcelRefToken(CellRef As String, ByRef Syntax As Integer, ByRef Row As Integer, ByRef Col As Integer) As Boolean

' Parses a single Excel Reference token

Dim Char1 As String = Mid(CellRef, 1, 1)
Dim Char2 As String = Mid(CellRef, Len(CellRef), 1)

If Char1 >= "A" And Char1 <= "Z" Then
If IsNumeric(Char2) Then
CellRange.PositionToRowColumn(CellRef, Row, Col)
Syntax = 3 ' CellRef specifies a cell reference like "BC3"
Return True
Else
If Char2 >= "A" And Char2 <= "Z" And Len(CellRef) <= 2 Then
Col = ExcelColumnCollection.ColumnNameToIndex(CellRef)
Syntax = 1 ' CellRef specifies a whole column selection syntax like "BD"
Return True
Else
Syntax = 0 ' Illegal syntax
Return False
End If
End If

Else ' CellRef is either numeric and specifies a single row, or else is invalid

If IsNumeric(CellRef) Then
Row = CInt(CellRef) - 1
Syntax = 2 ' whole row selection syntax like "321"
Return True
Else
Syntax = 0 ' illegal Syntax
Return False
End If
End If

End Function

Private Function Get_Excel_Col_By_Index(Col_Index As Integer) As String

' Converts a numeric column index numbered from 0 to 702 into an Excel column index
' with letter references from A ... Z, AA ... AZ, BA ... BZ, , ZA to ZZ

If Col_Index < 0 Or Col_Index > 702 Then Return ""
Return ExcelColumnCollection.ColumnIndexToName(Col_Index)

End Function

Private Function Get_Excel_Col_By_Name(ws As ExcelWorksheet, Colname As String, Optional Header_row As Integer = 0) As String

' Converts the first Colname found in the header row number Header_Row into an Excel column index
' with letter references from A ... Z, AA ... AZ, BA ... BZ, , ZA to ZZ

Dim headerCells = ws.Rows(Header_row).AllocatedCells
Dim colNumber As Integer

If String.IsNullOrWhiteSpace(Colname) Then Return ""

For colNumber = 0 To headerCells.Count - 1
If (headerCells(colNumber).Value).Equals(Colname) Then
Return ExcelColumnCollection.ColumnIndexToName(colNumber)
End If
Next
Return ""

End Function

End Class