0

I haven't gotten into macros yet which is why I am asking here. 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:

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 a lot 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).

Here's an 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:

Gradient example

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 a lot 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.

0

That took a while, but I think I can help you. I wrote three subroutines that can name the secondary diagonals. The first is the select_diagonal sub

Sub select_diagonal(matriz As Range, m As Integer, name As String)

Dim n As Integer, i As Integer, first As Boolean
Dim diag As Range

n = matriz.Rows.Count
'm must be at most 2n-1 (number of diagonals)
first = True

If m <= n Then
    For i = 0 To m - 1 Step 1
        If first Then
            Set diag = matriz.Item(1).Offset(i, m - i - 1)
            first = False
        Else
            Set diag = Union(diag, matriz.Item(1).Offset(i, m - i - 1))
        End If
    Next i

Else
    For i = (m Mod n) To m - (m Mod n) - 1 Step 1
        If first Then
            Set diag = matriz.Item(1).Offset(i, m - i - 1)
            first = False
        Else
            Set diag = Union(diag, matriz.Item(1).Offset(i, m - i - 1))
        End If
    Next i
End If

ActiveWorkbook.Names.Add name:=name, RefersTo:=diag

End Sub

It receives as parameters a square matrix as a range (your 60x60 range), an integer m (that must be at most 2*n-1, the number of diagonals of the matrix) and a string name to name the m-th secondary diagonal of the matrix.

There is also the name_range sub, that receives a square matrix as a range and loop every secondary diagonal of the matrix and gives it a named range (according to your instructions, the k-th secondary diagonal is named as "_k")

Sub name_range(matriz As Range)

    Dim n As Integer, ii As Integer
    n = matriz.Rows.Count

    For ii = 1 To 2 * n - 1
        Call select_diagonal(matriz, ii, "_" & CStr(ii))
    Next ii

End Sub

Finally, the subroutine you must run is simply the main sub. Just select your entire range and run this subroutine, to call the two others that will do the work for you.

Sub main()

    Dim matriz As Range

    Set matriz = Selection

    Call name_range(matriz)

End Sub

After that, simply apply the conditional formatting to every range. As you just said, you can manually apply the gradients after the ranges are made. My suggestion, however, is to apply it via a VBA procedure. Just specify the color bounds and, with the named ranges already defined, apply it to every named range.

PS: I couldn't find a way to do it without VBA. The biggest problem here is defining the named ranges for the secondary diagonal and using the color scaling with a dynamic range or with some criteria. Hope this helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.