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

 

[Norie]

Well you've not referenced a worksheet when you are using Cells so VBA will be looking at the active sheet which won't necessarily be sh.

Another problem is sh.color - I don't think color is a property of a worksheet.

I'm not sure what the correct property is for the tab colour as I don't have a version of Excel that allows that, but I do know it's not color.

 

[Jonmo1]

it's

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

 

[fry]

Hi

Thanks a lot guys. I've tried to include the code but I get the following,

application defined or object defined error????

My code is as follows,

For r = 11 To 13
If Cells(B, r) = "boc" Then
Sheets(intNewPO).Tab.ColorIndex = 35
End If
Next

 

[tom_crisp]

I think you need "double quotes" around your sheet name.

 

[fry]

I've just thought of another problem. the cells in question might not contain "boc" exactly so the test would fail. They might contain "boc - abcdef" which defeats the object of the test?!?!?

Is there a way to check the contents of a cell for "boc" anywhere in the cell???

 

[Jonmo1]

Maybe

For r = 11 To 13
On Error Resume Next
If Worksheetfunction.Search("boc", Cells(B, r)) > 0 Then
Sheets("intNewPO").Tab.ColorIndex = 35 
End If 
Next

 

[fry]

Hi jonmo

just tried your code and it appears to execute but entering boc in B11 doesn't do anything. I'm assuming color 35 is blue of course!?

 

[fry]

Thought I'd better put it in context in case anything above the routine is causing a problem so here is the code that sets up the new PO. I realise the tab is given a green colour before the new test but I'm not clever enough to integrate it all and as long as it works, thats fine.....

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
If WorksheetFunction.Search("boc", Cells(B, r)) > 0 Then
Sheets("intNewPO").Tab.ColorIndex = 35
End If
Next

 

[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 
If WorksheetFunction.Search("boc", Cells(r, "B")) > 0 Then 
Sheets("intNewPO").Tab.ColorIndex = 35 
End If 
Next