CODEWITHSUNDEEP
vbaexcel
qz_99
qz_99

Reputation: 175

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

Upvotes: 1

Views: 3112

Answers (2)

Crazy Brit
Crazy Brit

Reputation: 33

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

Upvotes: 1

Vityata
Vityata

Reputation: 43585

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:

enter image description here

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

Upvotes: 1

Related Questions