excel - in vba, start on worksheet1, press button to execute macro to display worksheet 2 and run additional code. worksheet2 do

After executing the following code, I see part of worksheet1 and part of worksheet2 when I expect to see all of worksheet2 and only worksheet2 (worksheet2 = "working sheet" in the example):

Dim  k As Integer
Dim sh1 As Worksheet: Set sh1 = Sheets("working sheet")

sheet_name = "working sheet"
Sheets(sheet_name).Range("B24:AO24").Value = ""

sh1.Visible = True
sh1.Select

For k = 3 To 41 Step 2

    sh1.Cells(24, k).Select
    If WorksheetFunction.IsNumber(Sheets(sheet_name).Cells(3, k).Value) Then
        Selection.Interior.Color = &H20FFFF         'set color to yellow
    Else
        Selection.Interior.Color = xlNone           'clear all color
    End If
    
Next k

. . . . .

After executing the following code, I see part of worksheet1 and part of worksheet2 when I expect to see all of worksheet2 and only worksheet2 (worksheet2 = "working sheet" in the example):

Dim  k As Integer
Dim sh1 As Worksheet: Set sh1 = Sheets("working sheet")

sheet_name = "working sheet"
Sheets(sheet_name).Range("B24:AO24").Value = ""

sh1.Visible = True
sh1.Select

For k = 3 To 41 Step 2

    sh1.Cells(24, k).Select
    If WorksheetFunction.IsNumber(Sheets(sheet_name).Cells(3, k).Value) Then
        Selection.Interior.Color = &H20FFFF         'set color to yellow
    Else
        Selection.Interior.Color = xlNone           'clear all color
    End If
    
Next k

. . . . .

• excel
• vba

Share Improve this question Follow edited Jan 30 at 17:10 Christopher Paul asked Jan 30 at 16:40 Christopher PaulChristopher Paul 755 bronze badges 7

• In general, you want to avoid using Select in your code. – cybernetic.nomad Commented Jan 30 at 16:47
• 1 Your code only deals with one worksheet? Where is the other one you mention? You also switch between "workbook" and "worksheet" which makes the question a little confusing. – Tim Williams Commented Jan 30 at 16:48
• I have multiple sheets in one workbook. Where did I refer to "workbook"? I find that if I append a msgbox after the code and press enter, "working sheet" displays fully. But as subsequent code changes the cells on "working sheet", the changes are not visible. Continuously adding msgbox is not a feasible solution for my application. – Christopher Paul Commented Jan 30 at 16:56
• I see where I incorrectly wrote workbook in my initial post. All such references in that post should be corrected to worksheet. – Christopher Paul Commented Jan 30 at 16:58
• 1 Then please edit your question – cybernetic.nomad Commented Jan 30 at 17:09

 |  Show 2 more comments

2 Answers 2

Sorted by: Reset to default 0

Maybe try unhiding after the updates?

Dim k As Long

With ThisWorkbook.Worksheets("working sheet") 'or ActiveWorkbook, etc
    .Range("B24:AO24").Value = ""
    For k = 3 To 41 Step 2
        .Cells(24, k).Interior.Color = _
            IIf(IsNumeric(.Cells(3, k).Value), &H20FFFF, xlNone)
    Next k
    .Visible = True
    .Select
End With

It seems I've found a solution: whenever I update something that I want to see on the screen while vba is still running, I execute

Application.ScreenUpdating = True 
Application.Wait (Now + TimeValue("0:00:01")) 
Application.ScreenUpdating = False