VBA not cell filling correctly in IF/THEN structure

[kmmsquared]

Hello,

I am using the following code below to filter a column and color the rows of that contain cells with the correct values (this occurs 3x in my code). My problem is the if e is nothing then the column still colors. I only want the rows to be colored if e is not nothing. Any suggestions would be greatly appreciated-there is probably something simple that I can't think of. Thanks!

With Sheets("Sheet1")
   lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
   s = Application.WorksheetFunction.Match("Esc", Range("1:1"), 0)
   e = Range("E2:E" & lngLastRow).SpecialCells(xlCellTypeVisible)      
 
Columns("E").AutoFilter Field:=1, Criteria1:="MAKE"
 
[COLOR=red]If Not e Is Nothing Then
Range(Cells(2, 1), Cells(lngLastRow, s)).SpecialCells(xlCellTypeVisible).Activate
With Selection.Interior
.ThemeColor = xlThemeColorAccent5
.TintAndShade = 0.599993896298105
End With
[/COLOR]End If

 

[Gary McMaster]

Not certain it's the problem but the End If should be before the End With.

Gary

 

[kmmsquared]

The code actually won't work if the end if is before the end with! Interesting suggestion though!

 

[Gary McMaster]

There are also 2 With statements and only 1 End With. At least in the sample you posted.

Gary

 

[kmmsquared]

I only posted a portion of my code--I have an end with at the end of the sub after the code cycles through the other 2 filters.

 

[kmmsquared]

If e is nothing, I want nothing to happen, but it still colors the column.

 

[Gary McMaster]

If e is nothing, I want nothing to happen, but it still colors the column.

The following line states the opposite.

If Not e Is Nothing Then

Try getting rid of the "Not"

Gary

 

[jim may]

Isn't the "IS NOTHING" designed to only speak to OBJECT VARIABLES?
AND.. OBJECT VARIABLES are ONLY set by using the Key Word "SET"
So... Wouldn't you need to say "Set e = ???"

Currently e is a normal variable, not as Object Variable.

 

[kmmsquared]

Got it to work. Thank you!

 

[jim may]

Sharing what you did to make it work is always better, so others can learn