If Statement checking

Okay, so I'm going moderately crazy here.

This is my If-statement. The cells in my data are formatted such that blank cells have a colorindex of 48 which is a dark grey.

Cells that are green are conditionally formatted, so have a ci of xlnone, but always have a value in them (len of cell value > 0).

Cells that are red are conditionally formatted, and have a ci of xlnone, but have no value in them (len of cell value = 0)

However, some users have since corrupted the data, and turned some cells into colorindex 3 (red) or colorindex 4 (green)

I'm trying to single out any red cell. This would be.. len of cell is 0, and color is either xlnone or 3.

This is my if statement. No matter what I try, it doesnt want to catch the red cells. I'm checking two cells at one time, so the IF statement goes something like
if ((main is 0) and color index is xlnone or 3) or ((offset is 0) and colorindex is xlnone or 3)

[vba]
If ((Len(cellVariable.value) = 0 And _
((cellVariable.Interior.ColorIndex = xlNone) Or _
(cellVariable.Interior.ColorIndex = 3))) _
Or (Len(cellVariable.Offset(0, 1).value) = 0) And _
(cellVariable.Offset(0, 1).Interior.ColorIndex = xlNone Or _
cellVariable.Offset(0, 1).Interior.ColorIndex = 3)) _
And Not (cellVariable.Interior.ColorIndex = 4) And Not _
(cellVariable.Offset(0, 1).Interior.ColorIndex = 4) Then
[/vba]
Note the "And not color = 4" because this was actually catching green formatted [colorindex 4] cells as well somehow and this was the only way I could get it to stop grabbing greens.

Edit: colorindex of dark gray was 48 not -4142...4142 was xlnone

Edit: Easier to read if statement

[vba]
cellcolor = cellVariable.Interior.ColorIndex
celloffcolor = cellVariable.Offset(0, 1).Interior.ColorIndex


If Sheet1.optionReds.value Then

'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

[/vba]

There may be some confusion as to what you are seeing (ci vs cond. format). You've said a blank cell should be gray (ci=48) but is conditionally formatted to show as red. Your IF statement wouldn't catch that.

Just write a routine to clear the existing formats and reapply your scheme to the data. This will classify the data how you intended rather than someone else's idea of what should be shown.

You better first check the colorindex of a cell you want to change, e.g.

[VBA]msgbox range("C5").interior.colorindex[/VBA]

[vba]
sub snb()
for each cl in cells.speciacells(4)
select case cl.interior.colorindex
case 0,3
cl.interior.colorindex=xlnone
end select
next
end sub
[/vba]

Quote:

---

Originally Posted by Teeroy

There may be some confusion as to what you are seeing (ci vs cond. format). You've said a blank cell should be gray (ci=48) but is conditionally formatted to show as red. Your IF statement wouldn't catch that.

Just write a routine to clear the existing formats and reapply your scheme to the data. This will classify the data how you intended rather than someone else's idea of what should be shown.

---

I specified in my first post that the cells are SUPPOSED to be conditionally formatted so that they would be XLnone, but some users have since corrupted their "database" and turned some of the "reds" into actually Colorindex 3 cells rather than letting the conditional formatting turn them "red". So essentially, a perfect db is Colorindex = xlnone and Empty =CF to Red, or Colorindex = xlnone and Not empty = CF to green, and totally unused cells are Colorindex = 48 with no CF, and empty.

This meant that there would only be 2 values for colorindex, xlnone or 48, and i could just grab the xlnones and be great. The problem is that now some users have added colorindex 3 and colorindex 4 to the DB, so I want my prog to be able to grab these.

edit: i should note that my program is not allowed write access to the DB, read only, so I cannot perform the error checking to fix users corruption, and just need to be able to read their garbage and turn it into data.

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.

Quote:

---

Originally Posted by Teeroy

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.

Code:

---

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

---