locked
apply formatting to named ranges using a macro RRS feed

  • Question

  • Dear Experts: 

    I would like to apply the below formatting to named ranges in the active worksheet in just one macro.

    The named ranges have the following make-up: 

    prod_range_01, prod_range_02, prod_range_03, prod_range_04, till prod_range_80. 

    Help is very much appreciated. Thank you very much in advance.

            

    Sub Format_Selection

    For Each cell In Selection

        If cell.Row Mod 2 = 1 Then ''highlights row 2,4,6 etc|= 0 highlights 1,3,5
            cell.Interior.Color = RGB(255, 255, 255)

        Else
            cell.Interior.Color = RGB(197, 217, 241)
        End If
    Next cell

    End Sub

    Monday, May 6, 2019 8:16 AM

Answers

  • Like this. I hope you're starting to see a pattern...

    Sub Format_Ranges()
        Dim i As Long
        Dim s As String
        Dim cell As Range
        For i = 1 To 80
            s = Format(i, "00")
            For Each cell In Range("prod_range_" & s)
                If cell.Row Mod 2 = 1 Then ''highlights row 2,4,6 etc|= 0 highlights 1,3,5
                    cell.Interior.Color = RGB(255, 255, 255)
                Else
                    cell.Interior.Color = RGB(197, 217, 241)
                End If
            Next cell
        Next i
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Iamgrateful Monday, May 6, 2019 2:15 PM
    Monday, May 6, 2019 10:39 AM

All replies

  • Like this. I hope you're starting to see a pattern...

    Sub Format_Ranges()
        Dim i As Long
        Dim s As String
        Dim cell As Range
        For i = 1 To 80
            s = Format(i, "00")
            For Each cell In Range("prod_range_" & s)
                If cell.Row Mod 2 = 1 Then ''highlights row 2,4,6 etc|= 0 highlights 1,3,5
                    cell.Interior.Color = RGB(255, 255, 255)
                Else
                    cell.Interior.Color = RGB(197, 217, 241)
                End If
            Next cell
        Next i
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Iamgrateful Monday, May 6, 2019 2:15 PM
    Monday, May 6, 2019 10:39 AM
  • Hi Hans, 

    again, thank you very much for your swift and professional help. 

    Yes, of course I see the pattern, but you know just one line with one incorrect string or term and the macro does not work. 

    Again, thank you very much for your valuable help. I really appreciate it. Regards, Andreas

    Monday, May 6, 2019 2:15 PM