CODEWITHSUNDEEP
vbaexcel
Tomislav Paj
Tomislav Paj

Reputation: 31

Excel VBA - issue with sheet selection

Below you can find my code. It is really basic, but so is the problem :) So, I need to color cells in sheet CALENDAR based on values in sheet DATABASE (x,y,z).

But, this code color correct cells, but in sheet DATABASE instead of CALENDAR. As you can see, I tried to activate and select sheet CALENDAR in and out of for loop, but it still doesn't work.

Pls, HELP!

Thans in advance"!

Sub calendar_fill()

Worksheets("DATABASE").Activate
Set sh = ThisWorkbook.Sheets("DATABASE")
Dim i As Long
Dim x As Long
Dim y As Long
Dim z As Long
Dim dummy As Long

i = sh.Range("A1", sh.Range("A1").End(xlDown)).Rows.Count

Sheets("CALENDAR").Select
Worksheets("CALENDAR").Activate

For j = 1 To i - 2

    x = Worksheets("DATABASE").Cells(2 + j, "S").Value
    y = Worksheets("DATABASE").Cells(2 + j, "T").Value
    z = Worksheets("DATABASE").Cells(2 + j, "U").Value

    dummy = Worksheets("CALENDAR").Cells(1, 1).Value
    Sheets("CALENDAR").Select
    Worksheets("CALENDAR").Activate

    'annoucement
    Range(Cells(j + 5, x + 3), Cells(j + 5, y - 1 + 3)).Interior.Color = 6750207

    'open
    Range(Cells(j + 5, y + 3), Cells(j + 5, z + 3)).Interior.Color = 5296274


Next j

End Sub

Upvotes: 0

Views: 504

Answers (2)

Pav El
Pav El

Reputation: 391

try

sh.Range(Cells(j + 5, x + 3), Cells(j + 5, y - 1 + 3)).Interior.Color = 6750207

I think there is no need to activete your sheet or ranges, since your sheet is already set.

Upvotes: 0

Rory
Rory

Reputation: 34045

You don't need to select or activate - just qualify your Range and Cells calls with the worksheet object:

Sub calendar_fill()
    Dim i                     As Long
    Dim x                     As Long
    Dim y                     As Long
    Dim z                     As Long
    Dim dummy                 As Long
    Dim sh                    As Worksheet
    Dim shCal                 As Worksheet

    Set sh = ThisWorkbook.Sheets("DATABASE")
    Set shCal = ThisWorkbook.Sheets("CALENDAR")

    i = sh.Range("A1", sh.Range("A1").End(xlDown)).Rows.Count


    For j = 1 To i - 2

        x = sh.Cells(2 + j, "S").Value
        y = sh.Cells(2 + j, "T").Value
        z = sh.Cells(2 + j, "U").Value

        With shCal
            dummy = .Cells(1, 1).Value

            'annoucement
            .Range(.Cells(j + 5, x + 3), .Cells(j + 5, y - 1 + 3)).Interior.Color = 6750207

            'open
            .Range(.Cells(j + 5, y + 3), .Cells(j + 5, z + 3)).Interior.Color = 5296274
        End With


    Next j

End Sub

Upvotes: 2

Related Questions