VBA to Find Maximum Value in a Range

Hello,

I am typing in the following code and getting an error "run time 1004 unable to find Max". Any ideas? Thanks

Code:

---

Sub ColorMaximumValue()
    Dim rng As Range, cell As Range
    Set rng = Range("A1:A5")
    Debug.Print Application.WorksheetFunction.Max("rng")
End Sub

---

Attachment 26931

Try

Code:

---

Application.WorksheetFunction.Max(rng)


---

without the quotes

Thanks Paul, "brain cramp" moment. Forgot that for variable names, you do no need the quotation marks. It worked great :)

Can be a little confusing

If had a range named "rng", then it would be sort of similar

Code:

---

Sub ColorMaximumValue()
    Dim rng As String
    Range("A1:A5").Name = "rng"
    Debug.Print Application.WorksheetFunction.Max(range("rng"))End Sub

---

You can use Tread Tools and mark this one [SOLVED]