If then statement

[Todd Huttenstine]

Here is what I have... The part of the code with the ***s
is the part messing up. I keep getting "Object doesnt
support this property or method). and it highlights the
line " If Application.CountIf(.Range("z1:z26"), "Name")

0 Then" How do I fix this?

Private Sub CommandButton1_Click()
' Example of using the GetAColor function
' This sub prompts for a color, then changes
' the color the selected cells

Dim UserColor As Long
If TypeName(Selection) <> "Range" Then
MsgBox "Select a range."

Exit Sub
End If
UserColor = GetAColor()
If UserColor <> False Then Selection.Interior.Color =
UserColor
Range("Z1").Select

With Range("Z1").Interior
If .ColorIndex <> Range("A2").Interior.ColorIndex
Then
Application.ScreenUpdating = False
Cells.Interior.ColorIndex = .ColorIndex
Range("areaProgram").Interior.ColorIndex = 2
Range("areastatsnames").Interior.ColorIndex = 6
End If

'**************************
If Application.CountIf(.Range("z1:z26"), "Name") > 0 Then
Range("area1").Interior.ColorIndex = .ColorIndex
Else
Range("area1").Interior.ColorIndex = 2
End If
'**************************

 

[Trevor Shuttleworth]

Todd

you don't show all of the code but you need to check out the scope of any
With ... End With statements.

In the particular statement causing the problem I don't think you need the
"." in ".Range("z1:z26")"

The only With I can see is "With Range("Z1").Interior" and I can't see the
End With

I think this will cause you other problems but one step at a time ...

Regards

Trevor

 

[Todd Huttenstine]

Hey I got it. I used = Range("Z1").Interior.colorindex
and that fixed it. I just had to go at it from a
different angle.