VBA Conditional formatting using dynamic range

[tigersden]

Hi,
I am trying to get the code below to work correctly. The IF statement checks the cells value in column F if it is blank then inserts a formula in column F of the current row. The value of this cell is then checked & formatting is applied.
Sometimes depending on the order of data entry it is not storing a formula but the result of that formula.
Is there a more efficient way to write the formula?
How can I get round the problem above.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iBgColour As Integer
Dim strName As String

    If Not Intersect(Target, Range("MyRange")) Is Nothing Then
    
    If Cells(Target.Row, 6).Value = "" Then
        Cells(Target.Row, 6).Formula = Cells(Target.Row, 1) * Cells(Target.Row, 2) * Cells(Target.Row, 3) * Cells(Target.Row, 4) * Cells(Target.Row, 5)
    End If
        Select Case Cells(Target.Row, 6).Value
            Case 1 To 5
                iBgColour = 6
                strName = "First Group"
            Case 6 To 10
                iBgColour = 12
                strName = "Second Group"
            Case 11 To 15
                iBgColour = 7
                strName = "Third Group"
            Case 16 To 20
                iBgColour = 53
                strName = "Fourth Group"
            Case 21 To 25
                iBgColour = 15
                strName = "Fifth Group"
            Case 26 To 30
                iBgColour = 42
                strName = "Sixth Group"
            Case Is > 31
                iBgColour = 41
                strName = "Out Of Range"
        End Select
        Cells(Target.Row, 6).Interior.ColorIndex = iBgColor
        Cells(Target.Row, 7).Value = strName

    End If
    strName = ""
End Sub

Thanks in advance

 

[pgc01]

Hi tigersden

You are not inserting a formula, you are writing a value in the cell, equal to the product of the first 5 cells in that row. If what you want is to insert a formula that multiplies the first 5 cells in that row:

Cells(Target.Row, 6).Formula = "=A" & Target.Row & "*B" & Target.Row & "*C" & Target.Row & "*D" & Target.Row & "*E" & Target.Row

or you can use the function PRODUCT

Cells(Target.Row, "F").Formula = "=PRODUCT(A" & Target.Row & ":E" & Target.Row & ")"

If I misunderstood, please explain.

Hope this helps
PGC