2

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.

template example
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. Example of the Gradient

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.

Improve this question
2
  • 3
    I'm voting to close this question as off-topic because it's not about Apple hardware or software. However, you should post the question in the Super User community instead.
    – Monomeeth ♦
    Nov 30, 2016 at 22:07
  • It depends - what OS are you running Excel? iOS - here is the place. Mac - here is the place. Windows - SU is the place. Web - up for grabs - could be web apps, could be here, could be SU.
    – bmike ♦
    Nov 30, 2016 at 23:30

1 Answer 1

Reset to default
4

This is pretty easy in Excel 2016 for Mac.

Set up your grid and select the range.

enter image description here

Define your three color conditional coloring to the range.

enter image description here

Enjoy

enter image description here

Improve this answer
2
  • The problem with this is that it still selects a square area then applies the gradient to all cells in selection. if you add more numbers in the blank spaces they will get colored aswell. I'm trying to get lines like the one you have shown but with a different gradient on each line to get an effect that shows the largest numbers on each diagonal instead of just the largest numbers on the sheet
    – Michael Scheyer
    Dec 1, 2016 at 13:06
  • there's nothing stopping you from setting up multiple ranges. It wouldn't be that hard to write a short python script or just type out the ranged by hand for the first three or seven diagonals. For an arbirtarily complicated setup - you'll want to export to csv and program the solution, @MichaelScheyer
    – bmike ♦
    Dec 1, 2016 at 13:15

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .