magelan
10-19-2012, 02:44 PM
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)
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
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
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
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)
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
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
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