simple for next loop wont work

fry

Active Member
Joined
Apr 25, 2007
Messages
411
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?? :(


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

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
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.:)
 

fry

Active Member
Joined
Apr 25, 2007
Messages
411
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,

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

tom_crisp

Board Regular
Joined
Jul 28, 2006
Messages
133
I think you need "double quotes" around your sheet name.
 

fry

Active Member
Joined
Apr 25, 2007
Messages
411
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Maybe
Code:
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

Active Member
Joined
Apr 25, 2007
Messages
411
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

Active Member
Joined
Apr 25, 2007
Messages
411
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.....:)



Code:
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Code:
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
 

Forum statistics

Threads
1,181,733
Messages
5,931,726
Members
436,800
Latest member
abowalid98

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top