Hi Karen,

I am assuming you are trying to check if the value in K43 exists in
the array of cells you have specified, i which case you would need
something like this:

Option Explicit
Private ProRataShareCell As String

Private Sub Worksheet_Activate()

Dim N As Long
Dim ProRataArray()

ProRataArray = Array("F30", "G30", "H30", _
"I30", "J30", "K30", "L30", "M30", "N30", _
"F36", "G36", "H36", "I36", "J36", "K36", _
"L36", "M36", "N36")

   For N = LBound(ProRataArray) To UBound(ProRataArray)
       If Me.Range("K43") = Range(ProRataArray(N)).Value Then

           ProRataShareCell = ProRataArray(N)
           Debug.Print ProRataShareCell
       End If
   Next N

End Sub

Or, use the Find method... something like:

Option Explicit
Private ProRataShareCell As Range

Private Sub Worksheet_Activate()

Dim ProRataRng As Range

Set ProRataRng = Union(Range("F30:N30"), Range("F36:N36"))

With ProRataRng
Set ProRataShareCell = .Find(What:=Me.Range("K43"), After:=ProRataRng
_
.Areas(ProRataRng.Areas.Count).Cells(ProRataRng _
.Areas(ProRataRng.Areas.Count).Cells.Count))
End With

End Sub

Which will avoid a loop and just find the first cell that matches.

Cheers,
Ivan.