macro to color top 10% in range
 

Hello.
I'm using Excel X (VBA5) and trying write a macro which will change the
interior cell color of those cells whose value is in the top ten percent of
the selected range of cells. I was able to do this quite easily with
conditional formatting using the percentile formula, but I want to be able to
do it with a macro and explicit formatting. The below macro returns a "sub or
function not defined" error when it gets to PERCENTILE:

Sub NLFI_TopTenPercent()
'
' having selected the cells before running,
' first create range from selection
'
Selection.Name = "TopTenPercent_Range"
'
' add Loop to check each cell in range
'
For Each Cell In Range("TopTenPercent_Range")
'
' check Cell value against values in Range
' shade cell Yellow if or =
'
If (Cell.Value = Percentile("TopTenPercent_Range", 0.9)) Then
Cell.Interior.Color = vbYellow
End If
Next Cell
End Sub

Maybe I need a dim statement at the top?
And and all help would be appreciated.

macro to color top 10% in range
 

Percentile is a worksheet function.

If (Cell.Value = WorksheetFunction.Percentile("TopTenPercent_Range" , 0.9))
Then

--
Regards,

OssieMac


"G.R." wrote:

Hello.
I'm using Excel X (VBA5) and trying write a macro which will change the
interior cell color of those cells whose value is in the top ten percent of
the selected range of cells. I was able to do this quite easily with
conditional formatting using the percentile formula, but I want to be able to
do it with a macro and explicit formatting. The below macro returns a "sub or
function not defined" error when it gets to PERCENTILE:

Sub NLFI_TopTenPercent()
'
' having selected the cells before running,
' first create range from selection
'
Selection.Name = "TopTenPercent_Range"
'
' add Loop to check each cell in range
'
For Each Cell In Range("TopTenPercent_Range")
'
' check Cell value against values in Range
' shade cell Yellow if or =
'
If (Cell.Value = Percentile("TopTenPercent_Range", 0.9)) Then
Cell.Interior.Color = vbYellow
End If
Next Cell
End Sub

Maybe I need a dim statement at the top?
And and all help would be appreciated.

macro to color top 10% in range
 

OssieMac -
Thank you very much for your reply. I tried it and got the following error:

"Method 'Percentile' of object 'WorksheetFunction' failed"

Any ideas?

macro to color top 10% in range
 

Hi again,

There was a syntax error. I tested the following and it works fine. Need to
specify range for the named range.

Sub NLFI_TopTenPercent()
Range("A1:A1000").Select
Selection.Name = "TopTenPercent_Range"
For Each Cell In Range("TopTenPercent_Range")
If (Cell.Value =
WorksheetFunction.Percentile(Range("TopTenPercent_ Range"), 0.9)) Then
Cell.Interior.Color = vbYellow
End If
Next Cell
End Sub

--
Regards,

OssieMac


"G.R." wrote:

OssieMac -
Thank you very much for your reply. I tried it and got the following error:

"Method 'Percentile' of object 'WorksheetFunction' failed"

Any ideas?

macro to color top 10% in range
 

OssieMac,
Brilliant! Worked perfectly. Thank you again for your time and attention.
I'm very grateful.