Problems getting "with selection" to work

Question

I'm working on a excel vba code to import and manipulate some data from CSV-file. Suddenly a part of my code didn't work any more though it had worked without problems before.

It is about range.select and afterward with selection.Interior.Pattern = xlSolid

I have tried to copy the same small part of the code to a different workbook and here it work just perfect.

Dim iPhase As Integer
iPhase = Application.WorksheetFunction.CountIf(Range("A:A"), "Phase")
Dim h As Integer
h = 1

Range("A6").Select

Do Until h > iPhase
    Cells.Find(What:="Phase", after:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate
    ActiveSheet.Range(ActiveCell, ActiveCell.Offset(0, 16)).Select
    With selection.Interior
        .Pattern = xlSolid
        .Interior.PatternColorIndex = xlAutomatic
        .Interior.ThemeColor = xlThemeColorAccent6
        .Interior.TintAndShade = 0
        .Interior.PatternTintAndShade = 0
    End With
    With selection.Font
        .Bold = True
    End With
    h = h + 1
Loop

I get a compile error: Expected function or variable @"selection.interior"

Don't use `.Select` and `.Selection`: [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, Sep 19 '19 at 09:42
Possible duplicate of [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Pᴇʜ, Sep 19 '19 at 09:42
Also two more things **[1.]** You are assuming that `Cells.Find` will return a result. Check if it found something or not. You may want to see [THIS](http://www.siddharthrout.com/index.php/2018/01/05/find-and-findnext-in-excel-vba/) on how to do that **[2.]** `I get a compile error: Expected function or variable @"selection.interior"` If you copied your code and pasted it as it is then your `selection` has a small `s`. That means you have declared a variable/module/class etc with the same name. You need to avoid that as well... — Siddharth Rout, Sep 19 '19 at 11:59

score 0 · Answer 1 · answered Sep 19 '19 at 13:22

The comments already identify the issues with your code; but here is an alternative using Filter and SpecialCells to select the visible data. Comments are contained in the code.

Sub FliterWithConditionalFormatting()
Dim rng As Range

'properly defing and reference your workbook and worksheet, change as requiried
Set rng = ThisWorkbook.Sheets("Sheet1").Range("A1").CurrentRegion

'The WITH..END WITH statement allows you to shorten your code and avoid using SELECT and ACTIVATE
With rng
     .AutoFilter Field:=1, Criteria1:="Phase", Operator:=xlAnd 'filter the rng

    'set the range, to conditionally format only the visible data, skipping the header row
    With .Range(Cells(2, 1), Cells(rng.Rows.Count, 17)).SpecialCells(xlCellTypeVisible)

        With .Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = xlThemeColorAccent6
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With

        With .Font
            .Bold = True
        End With
    End With

    .AutoFilter 'Remove the filter
End With
End Sub

Problems getting "with selection" to work

1 Answers1