Help with calculation

[Mike]

Hi. I need help with a calculation in the macro below.
I have an array of numbers in cells c26:f29. I need to
compare each value with the product of numbers contained
in column "A" and row "30". For instance, C26 would be
compared to A26 * F30. C27 would be compared to A27 *
F30. B26 would be compared to A26 * F31 . and so on.

The calculation I need help with is in the
variable "myKeyValue" below.

Can anyone help?

Thanks,
Mike.
--------------------------
Sub CreateOval2()
Dim myRange As Range
Dim myCell As Range
Dim myKeyValue As Variant
Dim myOval As Shape

Set myRange = Sheet1.Range("c26:f29")

For Each myCell In myRange.Cells
With myCell
myKeyValue = ??? HELP ???
If .Value < myKeyValue Then
Set myOval = .Parent.Shapes.AddShape
(Type:=msoShapeOval, _
Top:=.Top, Left:=.Left,
Width:=.Width, Height:=.Height)
myOval.Fill.Visible = msoFalse
myOval.Line.Weight = 1.5
myOval.Line.ForeColor.SchemeColor =
17
End If
End With
Next myCell

End Sub

 

[Vasant Nanavati]

Do you have a typo in your description? The pattern doesn't make sense. B26
is not even in your original range.

 

[Mike.]

Yes, I did have a typo in the description ... actually,
D26 would be compared to would be compared to A26 *
F31 ... Sorry about the typo. Would you know how to
write the formula?

Thanks again,
Mike.

 

[Vasant Nanavati]

Try:

myKeyValue = Range("A" & myCell.Row) * Range("F" & (27 + myCell.Column)

Of course, subsequent insertions of rows/columns may break this.

 

[Vasant Nanavati]

Sorry, missing the ending parentheses:

myKeyValue = Range("A" & myCell.Row) * Range("F" & (27 + myCell.Column))