CODEWITHSUNDEEP
excelvba
user3781528
user3781528

Reputation: 639

applying conditional formating formula to a cell in inserted row (invalid procedure call/argument run-time error 5)

I'm trying to apply a conditional formatting formula to a second column of an inserted row. The cell in column B in inserted row will change color if user enters text into that cell (if a cell is not blank)

The following code produces run-time error '5' (invalid procedure call/argument).

Thank you,

Public Sub insertNewRow(ByVal Target As Range)
    Dim thisWs As Worksheet, thisRow As Range, l As Double, r As Double, i As Long
    Dim tr As Long

    'setColors
    Application.ScreenUpdating = False
    Set thisWs = ActiveSheet
    Set thisRow = Target
    tr = thisRow.Row
    If tr > 2 Then

        With Target.Cells(1, 2)
            .Select
            Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(NOT(ISBLANK($B2))"
           Selection.FormatConditions(.FormatConditions.Count).Interior.Color = lBlu

            Target.Cells(1, 1).Value = Target.Cells(0, 1).Value 'copy down RunID/SampleID from row above
            With Target.Cells(1, 4)
                .Value2 = "New variant in row " & tr   'insert "New data" in first cell of current row
                .Interior.Color = vbYellow

            End With

        End With
        ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count).Activate
        thisWs.Activate 
        thisWs.Cells(1, 1).Select
    End If
    Application.ScreenUpdating = True
End Sub

Upvotes: 0

Views: 272

Answers (1)

Sorceri
Sorceri

Reputation: 8033

The formula was the first issue and then to set the range use the target address

Public Sub insertNewRow(ByVal Target As Range)
    Dim thisWs As Worksheet, thisRow As Range, l As Double, r As Double, i As Long
    Dim tr As Long

    'setColors
    Application.ScreenUpdating = False
    Set thisWs = ActiveSheet
    Set thisRow = Target
    tr = thisRow.Row
    If tr > 2 Then

        With Target.Cells(1, 2)
           .FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(ISBLANK(" & Target.Address & "))"
           .FormatConditions(.FormatConditions.Count).Interior.Color = lBlu

            Target.Cells(1, 1).Value = Target.Cells(0, 1).Value 'copy down RunID/SampleID from row above
            With Target.Cells(1, 4)
                .Value2 = "New variant in row " & tr   'insert "New data" in first cell of current row
                .Interior.Color = vbYellow

            End With

        End With
        ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count).Activate
        thisWs.Activate
        thisWs.Cells(1, 1).Select
    End If
    Application.ScreenUpdating = True
End Sub

Upvotes: 1

Related Questions