Excel VBA

css navigation by Css3Menu.com

Reset Selection

I had just rebuilt a worksheet for my customer; they added rows without asking advise and all the Validations quit working

Some places I had used one shade of yellow and others I used a brighter shade. My mission was to get them consistent, protect the cells (so they couldn’t change again), and set other attributes.

Sub fixDetail()
    Sheets("Details").Select
    Sheets("Details").UnProtect (SecretPassword)
    Dim X, Z
        For X = 1 To 13     'columns
            For Z = 1 To 56 'rows
              Cells(Z, X).Select
                If Cells(Z, X).Interior.ColorIndex = 6 Then

                    With Selection
                        .HorizontalAlignment = xlCenter 'Center
                        .VerticalAlignment = xlCenter
                        .Interior.ColorIndex = 36
                        .Locked = False  'unProtect
                    End With
                    With Selection.Font
                        .Name = "Calibiri"
                        .FontStyle = "Bold"
                        .Size = 12
                        .ColorIndex = xlAutomatic
                    End With
                Else
                    Selection.Locked = True
                End If
            Next Z
        Next X
            
      Sheets("Details").Protect (SecretPassword)
End Sub

'+-------------------------------------------------------------------------------------------------+

Sub LabelSecs()
    'Build labels for NET sheet
    Dim Sec, rRows, rCols
    Dim Scol
    rCols = 2
       Sheets("Net").Select
        For Sec = 7 To 304 Step 33
            rRows = 4
            rCols = 2
            For Scol = 3 To 41 Step 2
                Cells(Sec, Scol) = "='Rate Page'!" & Cells(rRows, rCols).Address
                rCols = rCols + 1
                If rCols = 7 Then
                    rCols = 2
                    rRows = rRows + 8
                End If
            Next Scol
        Next Sec
End Sub

'+---------------------------------------------------------------------

The second example is two nested For…Next loops.

© 2012-2024

Updated:  06/21/2024 07:42
This page added:  28 June 2012