Correcting Syntax on For Loop used to delete contionally highlighted duplicates.

[JHric]

I am trying to use a for loop to delete conditionally formatted duplicates, but I keep getting an error on the "End If". Can someone review this code to see what can replace the "End If" with correct syntax to makes the for loop operational?


Sub sbDelete_Rows_Based_On_Cell_Color()
Dim lRow As Long
Dim iCntr As Long
lRow = 14090
For iCntr = lRow To 1 Step -1
If Cells(iCntr, 1).Interior.ColorIndex = 3 Then ‘3=Red
Rows(iCntr).Delete
End If
Next
End Sub

[AB33]

Please use code tags with your code.

Sub sbDelete_Rows_Based_On_Cell_Color()
Dim lRow As Long
Dim iCntr As Long
lRow = 14090
For iCntr = lRow To 1 Step -1
   If Cells(iCntr, 1).Interior.ColorIndex = 3 Then '‘3 = Red
     Rows(iCntr).Delete
   End If
Next
End Sub

[judgeh59]

try removing the

" `=Red"

the ` is not used for the comments if that is what you are trying do...the single quote ' is what you want....

[judgeh59]

@AB33 --- the guru's type faster than the experts....

[AB33]

judgeh,
No really!
I have only added a comment to the line.
I think you are right, the OP meant to use a hyphen, not a comma.

[judgeh59]

AB33 - I thought the OP was trying the comment the 3=Red part and used the wrong character, I didn't think about make that part of the equation....actually, now I'm not sure what the OP was really trying to do with the 3=Red part...but I do know that the ` was causing the END IF error....

[AB33]

As far as I know, if the cell colour is formatted with conditional formatting, you can not evaluate the cell. In other words, the cell will not return true.

[JHric]

@Ab33, Do you have any other ideas or solved solutions I can look at. If worst comes to worst, I could color coding the values (ex. #3 for red) in an addition column, then sorting them. The last step would be mass deleting the conditionally formatted cells by highlighting the entire block.

[JHric]

It removed the End If Error, but no dice on the deleting.

[AB33]

judgeh,
The error is on the construction of if statement.

If Cells(iCntr, 1).Interior.ColorIndex = 3 Then ‘3=Red