Excel 2016. Automatically creating ranges based on cell contents then conditionally formatting the range I haven't gotten into macro's yet which is why I am asking on here. but I have a spreadsheet that I want to set gradients to a set of diagonal cells but it is very time consuming when you have 60+ columns. I have a template that has the cells numbered diagonally.Here is a small example of the template.


     this goes on currently to 60 both right and down. I can fill in the numbers easily but conditionally formatting them diagonally is going to take a long time. I want to know if there was a way to just select an area and say select all cells in this area that contain the same number and create a defined range named whatever number it is and if that range already exists then add that cell to that existing range. 

Example would be take all cells that contain "1" inside range "main" then create range "_1" and add cells to it. then repeat until all 60 ranges are complete.

then I would also need a way to take ranges "_1" through "_60" and easily add conditionally formatting to them for 3 color gradients to highlight the larger numbers in each range once I remove the numbers and replace with a formula. I might have to do this part manually but it would be alot easier if there were already ranges defined for each diagonal so I don't have to go and select area find cell containing "5", define range "_5", then conditionally format for each number 1-60(only 60 currently but it may end up being larger)

example of what one line with gradient looks like. will have gradient on each diagonal. might end up being 3 different colors to easily differentiate between each diagonal so you can tell at a glance what you are looking for.


I can do the gradients manually after the ranges are made.

I know this is most likely impossible but I would like to see as I have a quite a few of these to do and don't enjoy spending hours just formatting hundreds of cells manually.

a simplistic overview of what I need is

for all cells inside range "Main"
read cell
if range "-[cell]" exists
    [
        add cell to range
    ]
    else
    [
        define range named "-[cell]"
        add cell to range
    ]  

once all cells are in a range I would like something to do the gradients for me. something like "for each range -1 through -60, do 3 color conditional format"

I know there will be alot more to it than that but that is essentialy all I need.

Let me know if this is impossible. it probably is but there isn't any harm in asking.
This is pretty easy in Excel 2016 for Mac.

Set up your grid and select the range.



Define your three color conditional coloring to the range.



Enjoy