1

I have a macro with a loop that references the activecell. This works perfectly when I was building the code. However now I'm tidying up this excel template and am trying to execute this from another worksheet, which is like a menu/control sheet.

Everything works using the With Sheets apart from the part where I call on the activecell. Is there something I need to change?

With Sheets("EU Analysis")

        With .Range("C7:I5000")
            .Cells.ClearContents
            .Borders.LineStyle = xlNone
            .Interior.Color = 16777215
        End With

        With .Range("M7:Q21")
            .Cells.ClearContents
        End With

        With .Range("M23:N27")
            .Cells.ClearContents
        End With

        Dim r3 As Range
            Set r3 = .Range("C7:I7")
            r3.CopyFromRecordset rst4a

        Set r3 = .Range(r3, r3.End(xlDown).End(xlToRight))
         With r3
             .Borders(xlDiagonalDown).LineStyle = xlNone
             .Borders(xlDiagonalUp).LineStyle = xlNone
             .Borders(xlEdgeLeft).LineStyle = xlContinuous
             .Borders(xlEdgeRight).LineStyle = xlContinuous
             .Borders(xlEdgeTop).LineStyle = xlContinuous
             .Borders(xlEdgeBottom).LineStyle = xlContinuous
             .Borders(xlInsideVertical).LineStyle = xlContinuous
             .Borders(xlInsideHorizontal).LineStyle = xlContinuous
        End With

    RowCount = .Range("C" & Rows.Count).End(xlUp).Row - 6

        For i = 7 To RowCount + 6

            .Cells(i, 8).Select

            If ActiveCell.Value = "" Then
               ActiveCell.Value = "New"
               ActiveCell.HorizontalAlignment = xlCenter
               ActiveCell.Interior.Color = RGB(255, 192, 0)
            End If

        Next i

        For i = 7 To RowCount + 6

            .Cells(i, 7).Select

            If ActiveCell.Value = "" Then
               ActiveCell.Value = "Expired"
               ActiveCell.HorizontalAlignment = xlCenter
               ActiveCell.Interior.Color = RGB(141, 180, 226)
            End If

        Next i

End With

Thanks in advance

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Carlos80
  • 433
  • 15
  • 32
  • 6
    See [How to avoid using select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). It will be tremendously helpful. – BigBen Feb 01 '19 at 16:02
  • You can use `With Worksheets("EU Analysis")....End With` to hold your code. – Alex P Feb 01 '19 at 16:12
  • Thanks Alex P apologies I should have included the whole code because I am using With but for some reason when I get to the activecell part it does not work. – Carlos80 Feb 01 '19 at 16:21
  • It falls over at the .cells(i, 8).select – Carlos80 Feb 01 '19 at 16:23
  • 4
    See @BigBen's link - *don't use `Select` and rely on `ActiveCell`*. You aready have the cell you want to work with - go `With .Cells(i, 7)` and then `If .Value = "" Then` – Mathieu Guindon Feb 01 '19 at 16:23
  • Mathieu, amazing! exactly what I was looking for. Solved the problem, thanks so much!!! – Carlos80 Feb 01 '19 at 16:48

0 Answers0