| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Borders
Hi,
I made this macro and it works (see: Sub eentjestest()). If there's a cell that has no color, the cell value will be 1. But I also want to query on borders, so if the cell has borders then the cell must get the value 1. Does anybody know how to solve this? Sub eentjestest() Dim d As Range Set d = Range("A1:BE3000") For Each c In d x = c.Interior.ColorIndex If x = xlNone Then c.FormulaR1C1 = "1" End If Next c MsgBox ("Finished 1-test") End Sub The borders are something like this (but if I add it, it won't work): With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End Sub |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Borders
Hi,
In the meanwhile I've solved the problem below bij rewriting the macro see below. Now I want to use this macro in another workbook. Could someone help me with that? Dim d As Range Set d = Range("A1:z800") For Each c In d If c.Interior.ColorIndex = xlNone And _ c.Borders(xlEdgeLeft).Weight = xlThin And _ c.Borders(xlEdgeLeft).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeTop).LineStyle = xlContinuous And _ c.Borders(xlEdgeTop).Weight = xlThin And _ c.Borders(xlEdgeTop).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeBottom).LineStyle = xlContinuous And _ c.Borders(xlEdgeBottom).Weight = xlThin And _ c.Borders(xlEdgeBottom).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeRight).LineStyle = xlContinuous And _ c.Borders(xlEdgeRight).Weight = xlThin And _ c.Borders(xlEdgeRight).ColorIndex = xlAutomatic Then c.FormulaR1C1 = "1" End If Next c MsgBox ("Finished 1-test") End Sub anita schreef: Hi, I made this macro and it works (see: Sub eentjestest()). If there's a cell that has no color, the cell value will be 1. But I also want to query on borders, so if the cell has borders then the cell must get the value 1. Does anybody know how to solve this? Sub eentjestest() Dim d As Range Set d = Range("A1:BE3000") For Each c In d x = c.Interior.ColorIndex If x = xlNone Then c.FormulaR1C1 = "1" End If Next c MsgBox ("Finished 1-test") End Sub The borders are something like this (but if I add it, it won't work): With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End Sub |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Borders
Hi Don,
Thanks for the help! I want to use it only once in another workbook (x). (I don't want to copy it in this workbook (x), I've got the security on (of vba) and as far as I know it's not possible to get the security (of vba) off with a macro.) As I was working with the macro, I found out that I also use conditional formatting in some cells. Some cells are green because of conditional formatting, but the macro doesn't recognise this and fills the value 1 in. Do you perhaps also know how I can skip these green cells (because of the conditional formatting)? I have found something like this in the forums: FormatConditions(1).Interior.ColorIndex But this doesn't work in combination with the macro. Greetings, anita Don Guillett schreef: this idea may help shorten your formula. As to "another workbook", do you want to copy this macro to that workbook?, use it only once in another workbook? or make available to any workbook by placing in your PERSONAL.xls Sub placeOneIfNoColor() For Each c In Range("a1:z800") If c.Interior.ColorIndex = xlNone Then c.Value = 1 Next End Sub post back if you really need the borders also ====== these may prove helpful to color Sub bordersaround() Worksheets("Sheet1").Range("A1:D4").BorderAround _ ColorIndex:=3, Weight:=xlThick 'Selection.bordersaround End Sub -- Don Guillett SalesAid Software "anita" wrote in message oups.com... Hi, In the meanwhile I've solved the problem below bij rewriting the macro see below. Now I want to use this macro in another workbook. Could someone help me with that? Dim d As Range Set d = Range("A1:z800") For Each c In d If c.Interior.ColorIndex = xlNone And _ c.Borders(xlEdgeLeft).Weight = xlThin And _ c.Borders(xlEdgeLeft).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeTop).LineStyle = xlContinuous And _ c.Borders(xlEdgeTop).Weight = xlThin And _ c.Borders(xlEdgeTop).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeBottom).LineStyle = xlContinuous And _ c.Borders(xlEdgeBottom).Weight = xlThin And _ c.Borders(xlEdgeBottom).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeRight).LineStyle = xlContinuous And _ c.Borders(xlEdgeRight).Weight = xlThin And _ c.Borders(xlEdgeRight).ColorIndex = xlAutomatic Then c.FormulaR1C1 = "1" End If Next c MsgBox ("Finished 1-test") End Sub anita schreef: Hi, I made this macro and it works (see: Sub eentjestest()). If there's a cell that has no color, the cell value will be 1. But I also want to query on borders, so if the cell has borders then the cell must get the value 1. Does anybody know how to solve this? Sub eentjestest() Dim d As Range Set d = Range("A1:BE3000") For Each c In d x = c.Interior.ColorIndex If x = xlNone Then c.FormulaR1C1 = "1" End If Next c MsgBox ("Finished 1-test") End Sub The borders are something like this (but if I add it, it won't work): With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End Sub |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Borders
Open the other workbook and use with activeworkbook
The best way would be to use the same criteria as the CF Or, http://www.cpearson.com/excel/CFColors.htm -- Don Guillett SalesAid Software "anita" wrote in message oups.com... Hi Don, Thanks for the help! I want to use it only once in another workbook (x). (I don't want to copy it in this workbook (x), I've got the security on (of vba) and as far as I know it's not possible to get the security (of vba) off with a macro.) As I was working with the macro, I found out that I also use conditional formatting in some cells. Some cells are green because of conditional formatting, but the macro doesn't recognise this and fills the value 1 in. Do you perhaps also know how I can skip these green cells (because of the conditional formatting)? I have found something like this in the forums: FormatConditions(1).Interior.ColorIndex But this doesn't work in combination with the macro. Greetings, anita Don Guillett schreef: this idea may help shorten your formula. As to "another workbook", do you want to copy this macro to that workbook?, use it only once in another workbook? or make available to any workbook by placing in your PERSONAL.xls Sub placeOneIfNoColor() For Each c In Range("a1:z800") If c.Interior.ColorIndex = xlNone Then c.Value = 1 Next End Sub post back if you really need the borders also ====== these may prove helpful to color Sub bordersaround() Worksheets("Sheet1").Range("A1:D4").BorderAround _ ColorIndex:=3, Weight:=xlThick 'Selection.bordersaround End Sub -- Don Guillett SalesAid Software "anita" wrote in message oups.com... Hi, In the meanwhile I've solved the problem below bij rewriting the macro see below. Now I want to use this macro in another workbook. Could someone help me with that? Dim d As Range Set d = Range("A1:z800") For Each c In d If c.Interior.ColorIndex = xlNone And _ c.Borders(xlEdgeLeft).Weight = xlThin And _ c.Borders(xlEdgeLeft).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeTop).LineStyle = xlContinuous And _ c.Borders(xlEdgeTop).Weight = xlThin And _ c.Borders(xlEdgeTop).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeBottom).LineStyle = xlContinuous And _ c.Borders(xlEdgeBottom).Weight = xlThin And _ c.Borders(xlEdgeBottom).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeRight).LineStyle = xlContinuous And _ c.Borders(xlEdgeRight).Weight = xlThin And _ c.Borders(xlEdgeRight).ColorIndex = xlAutomatic Then c.FormulaR1C1 = "1" End If Next c MsgBox ("Finished 1-test") End Sub anita schreef: Hi, I made this macro and it works (see: Sub eentjestest()). If there's a cell that has no color, the cell value will be 1. But I also want to query on borders, so if the cell has borders then the cell must get the value 1. Does anybody know how to solve this? Sub eentjestest() Dim d As Range Set d = Range("A1:BE3000") For Each c In d x = c.Interior.ColorIndex If x = xlNone Then c.FormulaR1C1 = "1" End If Next c MsgBox ("Finished 1-test") End Sub The borders are something like this (but if I add it, it won't work): With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End Sub |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Borders
Hi Don,
Thanks! My macro now works as I was expecting to do. Don Guillett schreef: Open the other workbook and use with activeworkbook The best way would be to use the same criteria as the CF Or, http://www.cpearson.com/excel/CFColors.htm -- Don Guillett SalesAid Software "anita" wrote in message oups.com... Hi Don, Thanks for the help! I want to use it only once in another workbook (x). (I don't want to copy it in this workbook (x), I've got the security on (of vba) and as far as I know it's not possible to get the security (of vba) off with a macro.) As I was working with the macro, I found out that I also use conditional formatting in some cells. Some cells are green because of conditional formatting, but the macro doesn't recognise this and fills the value 1 in. Do you perhaps also know how I can skip these green cells (because of the conditional formatting)? I have found something like this in the forums: FormatConditions(1).Interior.ColorIndex But this doesn't work in combination with the macro. Greetings, anita Don Guillett schreef: this idea may help shorten your formula. As to "another workbook", do you want to copy this macro to that workbook?, use it only once in another workbook? or make available to any workbook by placing in your PERSONAL.xls Sub placeOneIfNoColor() For Each c In Range("a1:z800") If c.Interior.ColorIndex = xlNone Then c.Value = 1 Next End Sub post back if you really need the borders also ====== these may prove helpful to color Sub bordersaround() Worksheets("Sheet1").Range("A1:D4").BorderAround _ ColorIndex:=3, Weight:=xlThick 'Selection.bordersaround End Sub -- Don Guillett SalesAid Software "anita" wrote in message oups.com... Hi, In the meanwhile I've solved the problem below bij rewriting the macro see below. Now I want to use this macro in another workbook. Could someone help me with that? Dim d As Range Set d = Range("A1:z800") For Each c In d If c.Interior.ColorIndex = xlNone And _ c.Borders(xlEdgeLeft).Weight = xlThin And _ c.Borders(xlEdgeLeft).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeTop).LineStyle = xlContinuous And _ c.Borders(xlEdgeTop).Weight = xlThin And _ c.Borders(xlEdgeTop).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeBottom).LineStyle = xlContinuous And _ c.Borders(xlEdgeBottom).Weight = xlThin And _ c.Borders(xlEdgeBottom).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeRight).LineStyle = xlContinuous And _ c.Borders(xlEdgeRight).Weight = xlThin And _ c.Borders(xlEdgeRight).ColorIndex = xlAutomatic Then c.FormulaR1C1 = "1" End If Next c MsgBox ("Finished 1-test") End Sub anita schreef: Hi, I made this macro and it works (see: Sub eentjestest()). If there's a cell that has no color, the cell value will be 1. But I also want to query on borders, so if the cell has borders then the cell must get the value 1. Does anybody know how to solve this? Sub eentjestest() Dim d As Range Set d = Range("A1:BE3000") For Each c In d x = c.Interior.ColorIndex If x = xlNone Then c.FormulaR1C1 = "1" End If Next c MsgBox ("Finished 1-test") End Sub The borders are something like this (but if I add it, it won't work): With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End Sub |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Borders
glad to help
-- Don Guillett SalesAid Software "anita" wrote in message ups.com... Hi Don, Thanks! My macro now works as I was expecting to do. Don Guillett schreef: Open the other workbook and use with activeworkbook The best way would be to use the same criteria as the CF Or, http://www.cpearson.com/excel/CFColors.htm -- Don Guillett SalesAid Software "anita" wrote in message oups.com... Hi Don, Thanks for the help! I want to use it only once in another workbook (x). (I don't want to copy it in this workbook (x), I've got the security on (of vba) and as far as I know it's not possible to get the security (of vba) off with a macro.) As I was working with the macro, I found out that I also use conditional formatting in some cells. Some cells are green because of conditional formatting, but the macro doesn't recognise this and fills the value 1 in. Do you perhaps also know how I can skip these green cells (because of the conditional formatting)? I have found something like this in the forums: FormatConditions(1).Interior.ColorIndex But this doesn't work in combination with the macro. Greetings, anita Don Guillett schreef: this idea may help shorten your formula. As to "another workbook", do you want to copy this macro to that workbook?, use it only once in another workbook? or make available to any workbook by placing in your PERSONAL.xls Sub placeOneIfNoColor() For Each c In Range("a1:z800") If c.Interior.ColorIndex = xlNone Then c.Value = 1 Next End Sub post back if you really need the borders also ====== these may prove helpful to color Sub bordersaround() Worksheets("Sheet1").Range("A1:D4").BorderAround _ ColorIndex:=3, Weight:=xlThick 'Selection.bordersaround End Sub -- Don Guillett SalesAid Software "anita" wrote in message oups.com... Hi, In the meanwhile I've solved the problem below bij rewriting the macro see below. Now I want to use this macro in another workbook. Could someone help me with that? Dim d As Range Set d = Range("A1:z800") For Each c In d If c.Interior.ColorIndex = xlNone And _ c.Borders(xlEdgeLeft).Weight = xlThin And _ c.Borders(xlEdgeLeft).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeTop).LineStyle = xlContinuous And _ c.Borders(xlEdgeTop).Weight = xlThin And _ c.Borders(xlEdgeTop).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeBottom).LineStyle = xlContinuous And _ c.Borders(xlEdgeBottom).Weight = xlThin And _ c.Borders(xlEdgeBottom).ColorIndex = xlAutomatic And _ c.Borders(xlEdgeRight).LineStyle = xlContinuous And _ c.Borders(xlEdgeRight).Weight = xlThin And _ c.Borders(xlEdgeRight).ColorIndex = xlAutomatic Then c.FormulaR1C1 = "1" End If Next c MsgBox ("Finished 1-test") End Sub anita schreef: Hi, I made this macro and it works (see: Sub eentjestest()). If there's a cell that has no color, the cell value will be 1. But I also want to query on borders, so if the cell has borders then the cell must get the value 1. Does anybody know how to solve this? Sub eentjestest() Dim d As Range Set d = Range("A1:BE3000") For Each c In d x = c.Interior.ColorIndex If x = xlNone Then c.FormulaR1C1 = "1" End If Next c MsgBox ("Finished 1-test") End Sub The borders are something like this (but if I add it, it won't work): With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Borders not Printing | Excel Discussion (Misc queries) | |||
| Conditioning Formatting - Thicker Borders | Excel Worksheet Functions | |||
| Print cell borders with special 'skin' | Excel Discussion (Misc queries) | |||
| Cell borders and fill | Excel Worksheet Functions | |||
| Using angled column headings and borders | Excel Discussion (Misc queries) | |||
Linear Mode
