| Home |
| Search |
| Today's Posts |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Make 2 Macros Work as 1
Stick this within the with statement
..columns("h:iv").columns.columnwidth=15 -- Don Guillett SalesAid Software "Hazel" wrote in message ... Hi Don Is it that easy? of course it is when you know how eh! Just one query what would I insert to make all column width's on every sheet in the workbook from Column "H" in workbook be 15 instead of the default 8.43. Once again thank you for your time and effort it's made my day -- Merry Christmas and a Happy New Year. -- Many thanks hazel "Don Guillett" wrote: try NewMacro() 'UN tested For Each ws In Worksheets If ws.Name < "Sheet1" And ws.Name < "Sheet2" And ws.Name < "Sheet3" Then With ws ..Range("F3:F52").Copy .Range("H3") ..Columns("H:H").Insert Shift:=xlToRight ..Range("F3:F52").ClearContents 'what about this line for each sheet 'Sheets("Club Account").Range("A2:C26").ClearContents 'I don't understand what you want here but guess its for the ws? ..Range("A2:C26").ClearContents 'Application.Goto Sheets("K.Barber").Range("F3")'?? Dim cell As Range ..range("i3:i53").interior.colorindex=xlnone For Each c In .Range("I3:I53") If IsNumeric(c) and c.Value 5 Then .c.Interior.Color = RGB(0, 255, 0) next c End With End If Next ws End Sub -- Don Guillett SalesAid Software "Hazel" wrote in message ... Hi All Been trying for 3 hours to get the following 2 Macros to work together just keep getting errors after all my efforts, there are 26 sheets in the workbook Sub Macro2() For Each ws In Worksheets If ws.Name < "Sheet1" And ws.Name < "Sheet2" And ws.Name < "Sheet3" Then With ws .Range("F3:F52").Copy .Range("H3") .Columns("H:H").Insert Shift:=xlToRight .Range("F3:F52").ClearContents 'what about this line for each sheet Sheets("Club Account").Range("A2:C26").ClearContents Application.Goto Sheets("K.Barber").Range("F3") End With End If Next ws End Sub Sub Macro5() Dim cell As Range For Each cell In Range("I3:I53") 'test for a numeric value first If IsNumeric(cell.Value) Then 'test the value of the cell If cell.Value 5 Then 'color if greater than 5 cell.Interior.Color = RGB(0, 255, 0) Else 'clear color if not greater than 5 cell.Interior.ColorIndex = xlNone End If End If Next End Sub I can get them to run OK separately but Macro 5 will not run on all the worksheets, at the same time it would also be good if when running the macro's it sets all the column widths from Column H onwards to 15. -- Many thanks hazel |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Make 2 Macros Work as 1
glad to help. Merry/Happy "whatever turns you on" -- Don Guillett SalesAid Software "Hazel" wrote in message ... Hi Don Thanks -- can now go on holiday a happy bunny -- Many thanks hazel "Don Guillett" wrote: Stick this within the with statement ..columns("h:iv").columns.columnwidth=15 -- Don Guillett SalesAid Software "Hazel" wrote in message ... Hi Don Is it that easy? of course it is when you know how eh! Just one query what would I insert to make all column width's on every sheet in the workbook from Column "H" in workbook be 15 instead of the default 8.43. Once again thank you for your time and effort it's made my day -- Merry Christmas and a Happy New Year. -- Many thanks hazel "Don Guillett" wrote: try NewMacro() 'UN tested For Each ws In Worksheets If ws.Name < "Sheet1" And ws.Name < "Sheet2" And ws.Name < "Sheet3" Then With ws ..Range("F3:F52").Copy .Range("H3") ..Columns("H:H").Insert Shift:=xlToRight ..Range("F3:F52").ClearContents 'what about this line for each sheet 'Sheets("Club Account").Range("A2:C26").ClearContents 'I don't understand what you want here but guess its for the ws? ..Range("A2:C26").ClearContents 'Application.Goto Sheets("K.Barber").Range("F3")'?? Dim cell As Range ..range("i3:i53").interior.colorindex=xlnone For Each c In .Range("I3:I53") If IsNumeric(c) and c.Value 5 Then .c.Interior.Color = RGB(0, 255, 0) next c End With End If Next ws End Sub -- Don Guillett SalesAid Software "Hazel" wrote in message ... Hi All Been trying for 3 hours to get the following 2 Macros to work together just keep getting errors after all my efforts, there are 26 sheets in the workbook Sub Macro2() For Each ws In Worksheets If ws.Name < "Sheet1" And ws.Name < "Sheet2" And ws.Name < "Sheet3" Then With ws .Range("F3:F52").Copy .Range("H3") .Columns("H:H").Insert Shift:=xlToRight .Range("F3:F52").ClearContents 'what about this line for each sheet Sheets("Club Account").Range("A2:C26").ClearContents Application.Goto Sheets("K.Barber").Range("F3") End With End If Next ws End Sub Sub Macro5() Dim cell As Range For Each cell In Range("I3:I53") 'test for a numeric value first If IsNumeric(cell.Value) Then 'test the value of the cell If cell.Value 5 Then 'color if greater than 5 cell.Interior.Color = RGB(0, 255, 0) Else 'clear color if not greater than 5 cell.Interior.ColorIndex = xlNone End If End If Next End Sub I can get them to run OK separately but Macro 5 will not run on all the worksheets, at the same time it would also be good if when running the macro's it sets all the column widths from Column H onwards to 15. -- Many thanks hazel |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Make 2007 macros work in 2003 | Excel Discussion (Misc queries) | |||
| how can i make macros to work when in tools, costumize and option. | Excel Worksheet Functions | |||
| make macros (sub) work for add-in? | Excel Programming | |||
| How can I make Excel Macros created on PC work on Macintosh? | Excel Worksheet Functions | |||
| How to make the macros work for different Excel versions? | Excel Programming | |||
Linear Mode
