Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
macro to color top 10% in range
OssieMac,
Brilliant! Worked perfectly. Thank you again for your time and attention. I'm very grateful. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Make text color match cell color with macro? | Excel Discussion (Misc queries) | |||
condition chart color bar range | Charts and Charting in Excel | |||
Sort range by color | Excel Discussion (Misc queries) | |||
Sort range by color | Excel Discussion (Misc queries) | |||
color the interior of a range | Excel Worksheet Functions |