Using Conditional Formatting on graded colour scale for different groups

[Peter Davison]

I have a list of rankings in Column AB and I have ranked by a group in column L
My formula is - COUNTIFS($L$12:$L$100000,$L12,$Y$12:$Y$100000,">"&$Y12)+1)
Column Y is what it is ranking on
which all works spot on.
What I would like to do is show the red to green for each ranking within the group, at the moment it uses all the rankings on the red to green.
Is it possible to achieve this by using conditional formatting?
Thanks for any help you are able to give me.

 

[mikenelena]

Peter,

Please give this a try and let us know how it works out with your data.

Select the range of cells containing the rankings in Column AB.

Click on "Conditional Formatting" in the "Home" tab of the ribbon, and select "New Rule".

In the "New Formatting Rule" dialog box, select "Use a formula to determine which cells to format".

In the "Format values where this formula is true" field, enter the following formula (assuming your rankings start in cell AB2):

=$L2=L$2:INDEX(L:L,MATCH(1E+307,L:L))

This formula will check if the value in column L for the current row is equal to the value in cell L2, and will continue checking until the last row of column L with data.

1. Click on the "Format" button to select the formatting you want to apply to the cells that meet the condition.
2. In the "Format Cells" dialog box, go to the "Fill" tab and select the color you want to use (e.g., red).
3. Click "OK" to close the "Format Cells" dialog box.
4. Click "OK" to close the "New Formatting Rule" dialog box.
5. Repeat the above steps to add additional rules for each color you want to use (e.g., yellow, green) and adjust the formula to check for the corresponding ranking range.

Once you have set up the conditional formatting rules, the cells in column AB will be automatically formatted based on their ranking within each group in column L.

 

[Fluff]

What I would like to do is show the red to green for each ranking

If you referring to the inbuilt colour scales, then I don't think it's possible.

@mikenelena That formula will never work in Conditional Formatting, only in the sheet.

 

[Peter Davison]

I couldn't get that formula to work in the Conditional Formatting box.
Thanks for trying for me.
As Fluff says above I don't think I can make the graded colours work by department break in Column L.