Non Colored Cell

[adeel1]

Hi All
this code will run from outlook
i am unable to find first non yellow/Colored cell in excel, some its works and some time its given me wrong cell.

Code: Select all

Sub CopyNonYellow()
    Dim excelApp As Object
    Dim excelWorkbook As Object
    Dim excelWorksheet As Object
    Dim rangeToCopy As Object
    Dim nonYellowCell As Object
    
    
   
    Set excelApp = GetObject(, "Excel.Application")
    Set excelWorkbook = excelApp.Workbooks("SFA.xlsm")
    Set excelWorksheet = excelWorkbook.Worksheets("FOC Number")
    
 
    'numRows = InputBox("How many numbers do you want?", "Number of Rows") - 1
    
    ' Find the first non-yellow cell
    Set nonYellowCell = excelWorksheet.Range("A2:A100000").Find(What:="", LookIn:=xlValues, LookAt:=xlWhole, searchformat:=True)
   MsgBox nonYellowCell.Row

Adeel

[HansV]

You cannot search for a "non-yellow" cell but you can search for a cell with no fill color. You have to specify the FindFormat.
And since you appear to use late binding, you must replace the xl... constants with their values.

Code: Select all

Sub CopyNonYellow()
    Dim excelApp As Object
    Dim excelWorkbook As Object
    Dim excelWorksheet As Object
    Dim rangeToCopy As Object
    Dim nonYellowCell As Object
    Dim lngRow As Long

    Set excelApp = GetObject(, "Excel.Application")
    Set excelWorkbook = excelApp.Workbooks("SFA.xlsm")
    Set excelWorksheet = excelWorkbook.Worksheets("FOC Number")

    'numRows = InputBox("How many numbers do you want?", "Number of Rows") - 1

    ' Find the first non-yellow cell
    excelApp.FindFormat.Interior.ColorIndex = -4142 ' xlColorIndexNone

    Set nonYellowCell = excelWorksheet.Range("A2:A100000").Find( _
        What:="", _
        After:=excelWorksheet.Range("A100000"), _
        LookIn:=-4163, _
        LookAt:=1, _
        SearchFormat:=True)
    MsgBox nonYellowCell.Row
End Sub

[adeel1]

Perfect, lot of thx