IF condition based on Cell's color/ font color? 1

[davyre]

Hi, I have a problem. I have an Excel file contains ItemNo, PartNumber, Description, and Qty. This excel file will be imported to Access tables. In the PartNumber, there will be some colored cells (some are red, some are green, some are blue). The reason why I'm doing this is that it will be imported to different tables in Access (i.e red cells goes to Table A, green cells goes to Table B, blue cells goes to Table C).
What I am asking is how can I set this condition in VBA in Access? I hv read using cells.interior.colorindex, but then how do I know the code/value of the color that i'm using?
thanks for helping!

 

[SkipVought]

Hi,

So is the interior color based on some data in the table, or are they assigned using some external criteria?

If not, I'd code a function in Excel VBA to return a value based on the interior color.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[davyre]

Nope, the color is based on me, so I choose the color for the cells.
Done your suggestion in Excel VBA, now I got the color indexes
But in vba in access, where I want to create IF clause, it shows Invalid qualifier

Dim partNumber As String
Dim description As String
Dim qty As Long
Dim allBlank As Long
Dim excelLine As Long

Dim insTblAssyQry As String
Dim insTblPartQry As String
Dim insTblSubAssyQry As String

Dim excelApp As Excel.Application
Dim workbook As Excel.workbook
Dim worksheet As Excel.worksheet
Dim dbs As Database

Set excelApp = CreateObject("Excel.application")
Set workbook = GetObject(strInputFileName)
Set worksheet = workbook.Worksheets("Sheet1")
Set dbs = CurrentDb

allBlank = 0
excelLine = 1

Do While allBlank < 5
    partNumber = worksheet.cells(excelLine, "B")
    If IsNull(partNumber) Then
        allBlank = allBlank + 1
        excelLine = excelLine + 1
    Else
        allBlank = 0
        If [highlight #FCE94F]partNumber[/highlight].interior.colorindex = 3 And IsNull(worksheet.cells(excelLine, "A")) = False Then

any help?
THanks!

 

[SkipVought]

A STRING cannot have properties like a range object...

Do While allBlank < 5
    partNumber = worksheet.cells(excelLine, "B").value
    If partNumber = "" Then
        allBlank = allBlank + 1
        excelLine = excelLine + 1
    Else
        allBlank = 0
        If  worksheet.cells(excelLine, "B").interior.colorindex = 3 And worksheet.cells(excelLine, "A").value = "" = False Then

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[davyre]

thanks Skip!