Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For next question
Hi,
I have this code that I want to run in 15 sheets. I do not want to run where I have my macros. It is not giving me error, but still applying borders in Macro sheet. Please any idea to solve that? Sub Set_Borders() Dim sht As Worksheet For Each sht In ActiveWorkbook.Sheets If sht.Name < "Macro.xls" Then With sht.Range("A6:C6") .BorderAround LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic End With With sht.Range("A7:C11") .BorderAround LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic End With With sht.Range("A12:C16") .BorderAround LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic End With With sht.Range("A17:C21") .BorderAround LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic End With End If Next sht End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
For next question
Hi,
Your code shouldn'y have applied the formatting to the sheet called macro.xls and if it did then check the spelling is the same as in the code. I'd change your code slightly to this Sub Set_Borders() Dim sht As Worksheet For Each sht In ActiveWorkbook.Sheets If sht.Name < "Macro.xls" Then With sht .Range("A6:C6").BorderAround LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic .Range("A7:C11").BorderAround LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic .Range("A12:C16").BorderAround LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic .Range("A17:C21").BorderAround LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic End With End If Next sht End Sub Mike "Faboboren" wrote: Hi, I have this code that I want to run in 15 sheets. I do not want to run where I have my macros. It is not giving me error, but still applying borders in Macro sheet. Please any idea to solve that? Sub Set_Borders() Dim sht As Worksheet For Each sht In ActiveWorkbook.Sheets If sht.Name < "Macro.xls" Then With sht.Range("A6:C6") .BorderAround LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic End With With sht.Range("A7:C11") .BorderAround LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic End With With sht.Range("A12:C16") .BorderAround LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic End With With sht.Range("A17:C21") .BorderAround LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic End With End If Next sht End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
For next question
The sheet name on the tab is probably not capitalize the same as in your
macro OR there are extra spaces in the sheet name thsi should help from: If sht.Name < "Macro.xls" Then to: If Ucase(trim(sht.Name)) < MACRO.XLS" Then "Faboboren" wrote: Hi, I have this code that I want to run in 15 sheets. I do not want to run where I have my macros. It is not giving me error, but still applying borders in Macro sheet. Please any idea to solve that? Sub Set_Borders() Dim sht As Worksheet For Each sht In ActiveWorkbook.Sheets If sht.Name < "Macro.xls" Then With sht.Range("A6:C6") .BorderAround LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic End With With sht.Range("A7:C11") .BorderAround LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic End With With sht.Range("A12:C16") .BorderAround LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic End With With sht.Range("A17:C21") .BorderAround LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic End With End If Next sht End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
For next question
Thanks Mike and Joel,
I have checked what you said, and still doing the borders in Macro sheet... "Joel" wrote: The sheet name on the tab is probably not capitalize the same as in your macro OR there are extra spaces in the sheet name thsi should help from: If sht.Name < "Macro.xls" Then to: If Ucase(trim(sht.Name)) < MACRO.XLS" Then "Faboboren" wrote: Hi, I have this code that I want to run in 15 sheets. I do not want to run where I have my macros. It is not giving me error, but still applying borders in Macro sheet. Please any idea to solve that? Sub Set_Borders() Dim sht As Worksheet For Each sht In ActiveWorkbook.Sheets If sht.Name < "Macro.xls" Then With sht.Range("A6:C6") .BorderAround LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic End With With sht.Range("A7:C11") .BorderAround LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic End With With sht.Range("A12:C16") .BorderAround LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic End With With sht.Range("A17:C21") .BorderAround LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic End With End If Next sht End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
For next question
Do you actually have a sheet named "Macro.xls" or is it just named "Macro"?
If there is any mismatch at all in the sheet name, then the format code will apply to that sheet as well. The sheet name must me the same in case and content for the < operator to exclude that sheet. "Faboboren" wrote: Thanks Mike and Joel, I have checked what you said, and still doing the borders in Macro sheet... "Joel" wrote: The sheet name on the tab is probably not capitalize the same as in your macro OR there are extra spaces in the sheet name thsi should help from: If sht.Name < "Macro.xls" Then to: If Ucase(trim(sht.Name)) < MACRO.XLS" Then "Faboboren" wrote: Hi, I have this code that I want to run in 15 sheets. I do not want to run where I have my macros. It is not giving me error, but still applying borders in Macro sheet. Please any idea to solve that? Sub Set_Borders() Dim sht As Worksheet For Each sht In ActiveWorkbook.Sheets If sht.Name < "Macro.xls" Then With sht.Range("A6:C6") .BorderAround LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic End With With sht.Range("A7:C11") .BorderAround LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic End With With sht.Range("A12:C16") .BorderAround LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic End With With sht.Range("A17:C21") .BorderAround LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic End With End If Next sht End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
For next question
Is the .xls really part of the sheet name?
If it really matches, then I don't know what you problem could be. To get around the exact match issue, and at the same time to allow you to rename or reposition the worksheet, you could use If sh.Name < Sheet1.Name Then where Sheet1 would be replaced by the appropriate sheet that you have named "macro.xls"; you can see what sheet number to use in the VBA project explorer Good luck. Ken Norfolk, Va On Feb 17, 10:04*am, Faboboren wrote: Hi, I have this code that I want to run in 15 sheets. I do not want to run where I have my macros. It is not giving me error, but still applying borders in Macro sheet. Please any idea to solve that? Sub Set_Borders() * * Dim sht As Worksheet * * For Each sht In ActiveWorkbook.Sheets * * * * If sht.Name < "Macro.xls" Then * * * * With sht.Range("A6:C6") * * * * .BorderAround LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic * * * * End With * * * * With sht.Range("A7:C11") * * * * .BorderAround LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic * * * * End With * * * * With sht.Range("A12:C16") * * * * .BorderAround LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic * * * * End With * * * * With sht.Range("A17:C21") * * * * .BorderAround LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic * * * * End With * * * * End If * * Next sht *End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
For next question
JLGWhiz & Ken,
I deleted xls and now is working .... Thanks "JLGWhiz" wrote: Do you actually have a sheet named "Macro.xls" or is it just named "Macro"? If there is any mismatch at all in the sheet name, then the format code will apply to that sheet as well. The sheet name must me the same in case and content for the < operator to exclude that sheet. "Faboboren" wrote: Thanks Mike and Joel, I have checked what you said, and still doing the borders in Macro sheet... "Joel" wrote: The sheet name on the tab is probably not capitalize the same as in your macro OR there are extra spaces in the sheet name thsi should help from: If sht.Name < "Macro.xls" Then to: If Ucase(trim(sht.Name)) < MACRO.XLS" Then "Faboboren" wrote: Hi, I have this code that I want to run in 15 sheets. I do not want to run where I have my macros. It is not giving me error, but still applying borders in Macro sheet. Please any idea to solve that? Sub Set_Borders() Dim sht As Worksheet For Each sht In ActiveWorkbook.Sheets If sht.Name < "Macro.xls" Then With sht.Range("A6:C6") .BorderAround LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic End With With sht.Range("A7:C11") .BorderAround LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic End With With sht.Range("A12:C16") .BorderAround LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic End With With sht.Range("A17:C21") .BorderAround LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic End With End If Next sht End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|