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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
 
Upvote 0

Forum statistics

Threads
1,218,742
Messages
6,144,220
Members
450,531
Latest member
avril18

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
Back
Top