Non Colored Cell

adeel1
3StarLounger
Posts: 264
Joined: 04 Oct 2017, 15:47

Non Colored Cell

Post by 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

User avatar
HansV
Administrator
Posts: 77998
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Non Colored Cell

Post by 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
Best wishes,
Hans

adeel1
3StarLounger
Posts: 264
Joined: 04 Oct 2017, 15:47

Re: Non Colored Cell

Post by adeel1 »

Perfect, lot of thx :clapping: :clapping: :clapping: :chocciebar: