I've seen macros for this but I need a formula that pulls from a different worksheet.
Can someone help please?

Here's what I've got so far:


Function SmallCaps(myString As String)

Dim CellLength%, i%, DefaultSize
Dim OutString As String

OutString = myString

CellLength = Len(myString)
DefaultSize = 12

For i = 1 To CellLength
 With OutString.Characters(i, 1)
   If .Text = UCase(.Text) Then
      .Font.Size = DefaultSize
   Else
      .Text = UCase(.Text)
      .Font.Size = DefaultSize - 2
   End If
 End With
Next i

SmallCaps = CellLength

End Function