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

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

Borders
 

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

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

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

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

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