Borders

[anita]

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

 

[anita]

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:

 

[Don Guillett]

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("A14").BorderAround _
ColorIndex:=3, Weight:=xlThick
'Selection.bordersaround
End Sub

 

[anita]

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:

 

[Don Guillett]

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

 

[anita]

Hi Don,

Thanks! My macro now works as I was expecting to do.


Don Guillett schreef:

 

[Don Guillett]

glad to help