Sort Excel columns by cell color is not working RRS feed

  • Question

  • Below is the code I am using. Workbook exists and no issues with that. "Column I" has pink colored cells and this needs to be sorted on top. Below code didn't throw exception when I run but it is not sorting the excel column by cell color. What am I missing?

    Dim ws as Object = _ GetWorksheet(handle, workbookname, worksheetname, False) ' Do we have a sheet? sheetexists = ws IsNot Nothing ' No sheet? No entry. If Not sheetexists Then Return ' Do we have a sheet? sheetexists = ws IsNot Nothing ' No sheet? No entry. If Not sheetexists Then Return Try ws.Sort.SortFields.Clear() ws.Sort.SortFields.Add(ws.UsedRange.Columns("I"), _ Excel.XlSortOn.xlSortOnCellColor, Excel.XlSortOrder.xlAscending, , _ Excel.XlSortDataOption.xlSortNormal).SortOnValue.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbPink ' Sort Column C by Yellow 'ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2"), _ 'xlSortOnCellColor, xlAscending, , _ 'xlSortNormal).SortOnValue.Color = RGB(255, 255, 0) ws.Sort.SortFields.Add(ws.UsedRange.Columns("I"), Excel.XlSortOn.xlSortOnCellColor, Excel.XlSortOrder.xlAscending, Type.Missing, Excel.XlSortDataOption.xlSortNormal) 'Dim sort = ws.Sort ws.Sort.SetRange = ws.UsedRange 'ws.Sort.SortFields(0).SortOnValue.Color = Microsoft.Office.Interop.Excel.XlRgbColor.rgbPink ws.Sort.Header = Excel.XlYesNoGuess.xlYes ws.Sort.MatchCase = False ws.Sort.Orientation = Microsoft.Office.Interop.Excel.XlSortOrientation.xlSortColumns ws.Sort.SortMethod = Microsoft.Office.Interop.Excel.XlSortMethod.xlPinYin ws.Sort.Apply() Catch ex as Exception Throw End Try

    Thursday, March 14, 2019 2:33 PM

All replies

  • Hello,

    This does not appear to be VB.NET, please indicate as if not VB.NET I can move to the proper forum.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    profile for Karen Payne on Stack Exchange

    Thursday, March 14, 2019 3:42 PM
  • Hi,
    at first you must SET OPTION STRICT OFF for this program file because "SortOnValue.Color" works only with late binding when using Interop Microsoft Excel 16.0 Object Library.

    You use Range in UsedRange. In this case the Column "I" is the 9th column!

    My demo works (with yellow cell color):

    Option Strict Off
    Imports System.IO
    Imports Microsoft.Office.Interop
    Module Module08
      Sub Main()
          Dim c As New Demo
        Catch ex As Exception
        End Try
        Console.WriteLine("Continue enter key")
      End Sub
      Friend Class Demo
        Friend Sub Execute()
          Dim pathExe = Process.GetCurrentProcess().MainModule.FileName
          Dim xlApp As New Excel.Application
          Dim xlWb = xlApp.Workbooks.Open($"{Path.GetDirectoryName(pathExe)}\Module08Xl.xlsx")
          Dim xlsh = CType(xlWb.Worksheets(1), Excel.Worksheet)
          With xlsh
            Dim rng = CType(.UsedRange.Columns("I"), Excel.Range)
            Dim sortField = .Sort.SortFields.Add(rng,
            sortField.SortOnValue.Color = Excel.XlRgbColor.rgbYellow
            With .Sort
              .Header = Excel.XlYesNoGuess.xlGuess
              .MatchCase = False
              .Orientation = Excel.XlSortOrientation.xlSortColumns
              .SortMethod = Excel.XlSortMethod.xlPinYin
            End With
          End With
          xlApp = Nothing
        End Sub
      End Class
    End Module

    My ExcelSheet before

    and after

    Viele Grüsse / Best Regards
    Peter Fleischer (former MVP for Developer Technologies)
    Homepage, Tipps, Tricks

    Friday, March 15, 2019 7:25 AM