difficulty with percentage from worksheet as seen by VBA

[hippy]

Cells E3, I3, J3, and K3 are ideally formatted as percent for ease of
interpretation by users, however this creates a difficulty with the value of
K3 seen as "1" in the VBA code.

Even though for example by comparrisson the value of E3 on the spread sheet
is 62% and is seen in the VBA coce as 0.62 I can not see what is out of
order.

I have tried refromatting the cell K3, using a different cell, but no matter
what I have tried I continue to see the vlaue of cell K3 (55%) from the
spreadsheet being seen as "1" within the VBA code, which of course stops one
section of the code from activating. Any help would be appreciated, See code
below!

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

Dim ThsWk, LstWk, Avg, Trgt, Lb, Ub, Ooc As Long

'get variable values
ThsWk = Worksheets("Sheet1").Range("E3").Value
Lb = Worksheets("Sheet1").Range("I3").Value
Ub = Worksheets("Sheet1").Range("J3").Value
Ooc = Worksheets("Sheet1").Range("K3").Value

' test variable cases

If ThsWk > Ooc Then
With Worksheets("Sheet1").Range("C3").Value
Worksheets("Sheet1").Range("C3").Font.Name = "arial"
Worksheets("Sheet1").Range("C3").Value = "©"
Worksheets("Sheet1").Range("C3").Interior.ColorIndex = 3
End With
End If


If ThsWk > Ub And ThsWk < Ooc Then
With Worksheets("Sheet1").Range("C3").Value = "what?"
Worksheets("Sheet1").Range("C3").Value = ""
Worksheets("Sheet1").Range("C3").Font.Name = "arial"
Worksheets("Sheet1").Range("C3").Interior.ColorIndex = 3
End With
End If

If ThsWk < Ub And ThsWk > Lb Then
With Worksheets("Sheet1").Range("C3")
Worksheets("Sheet1").Range("C3").Value = ""
Worksheets("Sheet1").Range("C3").Font.Name = "arial"
Worksheets("Sheet1").Range("C3").Interior.ColorIndex = 6
End With
End If

If ThsWk < Lb Then
With Worksheets("Sheet1").Range("C3").Value
Worksheets("Sheet1").Range("C3").Value = ""
Worksheets("Sheet1").Range("C3").Font.Name = "arial"
Worksheets("Sheet1").Range("C3").Interior.ColorIndex = 4
End With
End If

End Sub

 

[JE McGimpsey]

You decared Ooc as Long, so it can contain only Long Ints.

Your other variables are declared as Variant since you didn't include a
type with them (unlike C, each variable needs a type even if they're all
on one line).