1004 Method Range of Object failed

[sheltiedeb]

Based on a value on Worksheet "A" (Range of "UserInput"), I need to change the font color on 2 worksheets. I get a runtime error of "Method range of object failed". I have tried adding Sheets("B").Activate before the Sheets("B").Select - that did not help. I am running out of ideas and I am about "googled out". Thanks for any hints/tips that you can throw my way.

Private Sub UserInput_Change()

Application.ScreenUpdating = False

Dim rng_TemplateObjects As Range
Set rng_TemplateObjects = Worksheets("A").Range("UserInput")

Select Case rng_TemplateObjects

Case "BUDGET"
Range("A!A10:A14").Select
Selection.Font.ColorIndex = 3
Sheets("B").Select
Range("B!A6:B6").Select <----- error on this line
Selection.Font.ColorIndex = 3

End Select

End Sub

 

[Kurt]

Error

Hello,

Get rid of the B! in the range reference.

You are trying to link to the worksheet and you are already there.

Kurt

 

[WillR]

Have you tried

 Sheets("B").Range("A6:B6").Font.ColorIndex = 3

 

[Richard Schollar]

Hi Sheltiedeb

Welcome to the board!

Try this:

Private Sub UserInput_Change()

Application.ScreenUpdating = False

Dim rng_TemplateObjects As Range
Set rng_TemplateObjects = Worksheets("A").Range("UserInput")

Select Case rng_TemplateObjects

Case "BUDGET"
Sheets("A").Range("A10:A14").Font.ColorIndex = 3
Sheets("B").Range("A6:B6").Font.ColorIndex = 3

End Select

End Sub

I must admit, I don't see why you are using a SELECT statement (a simple IF statement should suffice) but it will still work.

Best regards

Richard