'VBA - ActiveCell.Interior.ColorIndex -4142 error by conditional formatting

I have a check which ensures that no cells in a row are filled with Red(colorindex 3) before carrying on with the function however the Interior.ColorIndex is weirdly returning '-4142'. Anyone experienced this before/notice an error in my code?

If UCase(ActiveCell.Offset(0, -1).Value) = "X" Then

  For i = 0 To 7
    If ActiveCell.Offset(0, i).Interior.ColorIndex = 3 Then
        MsgBox "Correct fields highlighted in red."
        Exit Sub

    End If
   Next
    ident = ActiveCell.Value
    verNo = ActiveCell.Offset(0, 1).Value
    title = ActiveCell.Offset(0, 2).Value
    status = ActiveCell.Offset(0, 3).Value
    location = ActiveCell.Offset(0, 4).Value
    appDate = ActiveCell.Offset(0, 6).Value
    ccRef = ActiveCell.Offset(0, 7).Value

Where I initially add the highlighting:

With ThisWorkbook.Worksheets("Document Index").Range("B5:B500")
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(($B5=""""), 
$A5=""X"")"
    .FormatConditions(1).Interior.ColorIndex = 3
    .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(($B5<>""""), 
$A5=""X"")"
   .FormatConditions(2).Interior.ColorIndex = 37
End With

vba excel

Solution 1:^[1]

There are plenty of reasons why the code does not work. And all of them are hidden in the part of the code that you do not show...

In general, just guessing, is that you do not have writing access to the sheet with the ActiveCell. Thus, you have to unlock it.

Another option can be, that you have selected a Chart, and not a worksheet.

A third option is that you are making some other tricks. In general, a plain code like this works:

Sub Test
    ActiveCell.Offset(0,0).interior.ColorIndex =3
End Sub

But I really think that the error is the first one I have pointed out, I get it when I lock the sheet with the ActiveCell:

Concerning color index in format condition, you should use the nice Functions, provided by Mr. Pearson:

Function ColorIndexOfCF(Rng As Range, _ 
    Optional OfText As Boolean = False) As Integer

Dim AC As Integer
AC = ActiveCondition(Rng)
If AC = 0 Then
    If OfText = True Then
       ColorIndexOfCF = Rng.Font.ColorIndex
    Else
       ColorIndexOfCF = Rng.Interior.ColorIndex
    End If
Else
    If OfText = True Then
       ColorIndexOfCF = Rng.FormatConditions(AC).Font.ColorIndex
    Else
       ColorIndexOfCF = Rng.FormatConditions(AC).Interior.ColorIndex
    End If
End If

End Function

http://www.cpearson.com/Excel/CFColors.htm

Solution 2:^[2]

-4142 is the "no colour" value (xlColorIndexNone)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Community
Solution 2	juagicre

'VBA - ActiveCell.Interior.ColorIndex -4142 error by conditional formatting

Solution 1:[1]

Solution 2:[2]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]