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.
Thanks in advance
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