simple for next loop wont work

[fry]

Hi all, and yes this is pretty basic, but then so am I!

I'm trying to insert some code into a purchase order sheet I have so that it changes the tab colour depending on the contents of one of three cells.

I have code already that does several things (thanks to help from peeps on here) so I thought I'd just pop this code in the appropriate point.............no such luck.

Can anyone help??

for r = 11 to 13
if Cells(B,r) = "boc" Then
sh.color="blue"   
End if 
Next

 

[fry]

Hi jonmo

I tried your revision and I still dont get a blue tab???????????

I tried boc in B11, B12 & B13 but to no avail.....there's gremlins in the works somewhere!!

 

[Jonmo1]

Try just changing this line

Sheets("intNewPO").Tab.ColorIndex = 35

to

Activesheet.Tab.ColorIndex = 35

 

[fry]

Hi jonmo

semi-success!

It does now turn blue but unfortunately it doesn't matter whats in the cell, it turns blue anyway?????????

 

[Jonmo1]

Dim intNewPO As Integer 
Dim strActiveSheet As String 
intNewPO = Sheets(4).Name + 1 
Sheets(1).Copy After:=Sheets(1) 
ActiveSheet.Name = intNewPO 
strActiveSheet = ActiveSheet.Name 
ActiveSheet.Tab.ColorIndex = 43 

For r = 11 To 13 
On Error Resume Next
x = WorksheetFunction.Search("boc", Cells(r, "B"))
if x > 0 Then
ActiveSheet.Tab.ColorIndex = 35 
End If 
Next

 

[fry]

Works a treat!!

Thanks a lot Jonmo!!

Have a great day...

P.S. I don't suppose you know the code for closing a workbook by any chance. My worksheet is only used by about 6 people but sometimes someone leaves it open therefore stopping other people from using it so I'm thinking of automatically shutting it down. Thanks very much once again.

 

[Jonmo1]

Look at the Macro Recorder. tools - Macros - Record New Macro..


about 1/3 of the VBA code I know, I learned with that...

Of coarse, make sure you start recording the macro in a workbook OTHER THAN the one you close...

 

[fry]

Cheers jonmo, a good idea..........speaking of which did you use any particular book to learn further???

 

[Jonmo1]

Nope, just the Macro Recorder and this Forum.

Another great way to learn is to challenge yourself. Try to make a game.

I made a Chess Game and a Sudoku Game. That helped me learn alot.

 

[fry]

Hi Jonmo

I just tried the workbook shutdown like you suggested but upon choosing FILE|CLOSE it asks if I want to save the workbook (which I don't want) and I can't get rid of that.

Regardless of that I carried on and then went back to the macro to find this...

Sub shutdown()
'
' shutdown Macro
' Macro recorded 24/07/2007 by Pete
'

'
End Sub

 

[Jonmo1]

activeworkbook.close - will close whichever workbook is currently active.

workbooks("boookname.xls").close - will close a specified workbook...


adding
application.displayalerts = false before that will make it not prompt for save.

so

application.displayalerts = false
workbooks("bookname.xls").close
application.displayalerts = true