If Statement checking

[magelan]

The question you haven't answered is why do you care what color they are? If the data is valid the CF will format it so just use @SNB's routine to clear the user formatting.

Also CF always overrides colorindex so you can't be conditionally formatting to xlNone as this would override any user change of colorindex.

I guess I should clarify. again.

My prog is in a separate workbook - i have readonly access to the database workbook cells.

All of the cells in the workbook started out as colorindex-48. This meant that no action was happening for that cell.

When a cell becomes relevant to them, they change the colorindex to xlnone and apply their CF rule to it. This automatically turns it CF-red because it is empty. [but still xlnone in terms of colorindex]

If the user enters something into the cell, it becomes CF-Green [but still xlnone in terms of colorindex]

In a perfect sheet, this means that I am only ever reading colorindexs of 48 or xlnone so this entire thing becomes a non issue because I only grab cells that are colorindex=xlnone.

HOWEVER - users have made the sheet not-perfect by updating it improperly. Instead of making a cell active by turning its index into xlnone and then putting stuff in it, they have been filling it red or green however they please.

What I want is to be able to find the fill-red and fill-green. Not the CF-red or CF-green.

For red - I figured this would be as easy as doing "(Grab 3 and xlnone) and not empty" but I cant quite get the If statement to do it right.


EDIT:

Nevermind, nightmare is over.

Dim cellcolor As Long ' less crazy
    Dim celloffcolor As Long 'these are to make that if statement less crazy
    
    
...
...
                'if length is 0 and color is none or 3
                If ((Len(cellVariable.value) = 0) And (cellcolor = xlNone Or cellcolor = 3)) _
                Or ((Len(cellVariable.Offset(0, 1).value) = 0) And (celloffcolor = xlNone Or celloffcolor = 3)) Then