Excel VBA .Range as a variable and looping

  Kiến thức lập trình

The attached is a VERY simple workbook of a timesheet program I am working on. I am trying to figure out how to possibly use the .Range(variable).whatever where the variable could be made a loop. I need to loop from 1 to 7 for days of the week and have many variables. The Start and Stop in the attached are just for example. I need the variables to be global constants as they feed other subroutines or functions. I have the hard coded variables remarked out that worked but was hoping to take the variable prefix and do a for loop to concatenate together with the number to reduce the number of lines.

I tried the hard coded which works and tried the attached code with a few variations of using a temp string variable that I join and then use the temp.

The following code is in Module1

'public variables are used in actual project since variables can be used in other routines`  
Public Const Start1 As String = "B2:B7"
Public Const Stop1 As String = "C2:C7" 
Public Const Start2 As String = "D2:D7" 
Public Const Stop2 As String = "E2:E7"

The following code is on Worksheet Main, where cell B1 has a dropdown with the values Bold and Not Bold are located. There are two other Worksheets, one named TS1 and the other TS2. The code that is remarked out works, the For loop gives a Run-time error ‘1004’: Method ‘Range”’ of object ‘_Worksheet’ failed. I have tried several variations.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim sht As Worksheet
Dim x As Long


If Intersect(Target, Range("B1")) Is Nothing Then
    'nothing
ElseIf Target = "Bold" Then
    For Each sht In ThisWorkbook.Worksheets
        'sht.Range(Start1).Font.Bold = True
        'sht.Range(Stop1).Font.Bold = True
        'sht.Range(Start2).Font.Bold = True
        'sht.Range(Stop2).Font.Bold = True
        For x = 1 To 2
            sht.Range("Start" & x).Font.Bold = True
            sht.Range("Stop" & x).Font.Bold = True
        Next x
    Next sht
    MsgBox "Text bold is on"

ElseIf Target = "Not Bold" Then
    For Each sht In ThisWorkbook.Worksheets
        'sht.Range(Start1).Font.Bold = False
        'sht.Range(Stop1).Font.Bold = False
        'sht.Range(Start2).Font.Bold = False
        'sht.Range(Stop2).Font.Bold = False
        For x = 1 To 2
            sht.Range("Start" & x).Font.Bold = False
            sht.Range("Stop" & x).Font.Bold = False
        Next x
    Next sht
    MsgBox "Text bold is off"
End If

End Sub

LEAVE A COMMENT