quick help?

[VulcanJedi]

What's wrong with this code?
It should loop through multi- sheets, highlighing range yellow: This code currently runs on the MAIN sht(which isn't to be touched.) otherwise, activesheet or explicitly prefixing 'range' or 'cells' causes a 1004 error / applicatoin crash?
Just trying to highlight suspect row of 10 cells across. Done this before and don't understand why its crashing now. All variables contain values... help?
Range(Cells(a + xtraRows, 1), Cells(a + xtraRows, TPN + aux + xtraCols)).Interior.ColorIndex = 6

Tranpkp

 

[Zathras]

Just guessing since you didn't show much code, you are executing the statement in a loop and you have sheets other that worksheets in the book. Have you added a chart sheet recently?

Try something like this:
[blue]

  For Each sht In Worksheets
   If sht.Type = xlWorksheet Then
     With sht
      .Range(.Cells(a + xtraRows, 1), .Cells(a + xtraRows, TPN + aux + xtraCols)).Interior.ColorIndex = 6
     End With
   End If
  Next

[/color]

 

[VulcanJedi]

Thanks I tried using WITH Before, and didn't work. Also didn't need to on seperate program. Must have missed something, seems to work now.Can anybody explain WITH clearly? I thought you only need it to save space/code. Didn't think there was functionality differnce.

Tranpkp

 

[SkipVought]

The biggest reason to use With...End With is to make your code run more efficiently. Each time the code runs, the compiler evaluates each node in the statement. When you use With, that node(s) do not need to be reevaluated within the block.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[VulcanJedi]

Still confused, based on that, code should still run, albeit perhaps 'inefficiently'? Especially since I swear it does in different app.

Tranpkp