Excel VBA - run-time error '1004: Select method of Range class failed -

i have following excel vba code,

private sub worksheet_calculate()     mymacro end sub      public sub mymacro()       if range("c4").value = "sales"              range("e4:ab5").select selection.numberformat = "$#,##0.0"       else               range("e4:ab5").select selection.numberformat = "#,##0"      end if  end sub 

my problem cell "c4" references drop down menu in different sheet via vlookup. each time following error,

'run-time error '1004: select method of range class failed' , not work. 

if cell "c4" references same sheet works, need reference different sheet.

your question isn't clear if want c4 in different sheet (i have used sheet2 example), , format activesheet (no need select) try:

public sub mymacro() dim ws worksheet set ws = sheets("sheet2")  if ws.range("c4").value = "sales"     range("e4:ab5").numberformat = "$#,##0.0" else     range("e4:ab5").numberformat = "#,##0" end if  end sub