CODEWITHSUNDEEP
vbaexcel
ArnoldasM
ArnoldasM

Reputation: 206

Excel VBA removes equal sign from formula

I have an issue with vba code. I don't know why, but it removes equal sign in front of formula... Maybe anyone knows how to fix it?

enter image description here

Here is full source code of Excel Sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range
    For Each c In Target.Cells
        If Not Intersect(c, Range("X:AI")) Is Nothing Then
            If c > Range("H" & c.Row).Value Or c < Range("G" & c.Row).Value Then
                c.Font.ColorIndex = 3
            ElseIf c <= Range("H" & c.Row).Value And c >= Range("G" & c.Row).Value Then
                c.Font.ColorIndex = 10
            End If


            Dim frml1 As String
            Dim frml2 As String
            frml1 = "LARGE(X" & c.Row & ":AI" & c.Row & ";1)"
            frml2 = "SMALL(X" & c.Row & ":AI" & c.Row & ";1)"
            Range("AK" & c.Row).Value = "=AVERAGE(X" & c.Row & ":AI" & c.Row & ")"
            Range("AJ" & c.Row).Value = "=" & frml1 & " - " & frml2
        ElseIf Not Intersect(c, Range("AL:AM")) Is Nothing Then
            If c > Range("K" & c.Row).Value Or c < Range("J" & c.Row).Value Then
                c.Font.ColorIndex = 3
            ElseIf c <= Range("K" & c.Row).Value And c >= Range("J" & c.Row).Value Then
                c.Font.ColorIndex = 10
            End If
        ...
        ElseIf Not Intersect(c, Range("AU:AU")) Is Nothing Then
            If c > Range("Q" & c.Row).Value Or c < Range("P" & c.Row).Value Then
                c.Font.ColorIndex = 3
            ElseIf c <= Range("Q" & c.Row).Value And c >= Range("P" & c.Row).Value Then
                c.Font.ColorIndex = 10
            End If
        ElseIf Not Intersect(c, Range("AY:AY")) Is Nothing Then
            If c > Range("T" & c.Row).Value Or c < Range("S" & c.Row).Value Then
                c.Font.ColorIndex = 3
            ElseIf c <= Range("T" & c.Row).Value And c >= Range("S" & c.Row).Value Then
                c.Font.ColorIndex = 10
            End If
        End If
    Next c
End Sub

Upvotes: 3

Views: 1379

Answers (2)

Vityata
Vityata

Reputation: 43585

if you are coding like this in Excel, it is a good idea to be aware of the following:

?application.PathSeparator
?application.DecimalSeparator
?Application.International(xlFormula)

Write them in the immediate window of the VB Editor, to see your results. Then you may use them in VBA code, which would work lets say both in Germany and in the USA.

Upvotes: 1

ClintB
ClintB

Reputation: 509

You have ";" in your formula instead of ",". Perhaps because your formula is erroring its removing it.

Upvotes: 3

Related Questions