VBA Conditional formatting using dynamic range

tigersden

Board Regular
Joined
Oct 5, 2005
Messages
91
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.

Code:
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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:

Code:
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

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

If I misunderstood, please explain.

Hope this helps
PGC
 

Forum statistics

Threads
1,136,308
Messages
5,674,993
Members
419,541
Latest member
freddyboots

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top