About Us

Don Guillett

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Borders not Printing	L Lightner	Excel Discussion (Misc queries)	0	July 18th 06 11:33 PM
Conditioning Formatting - Thicker Borders	Fancy1	Excel Worksheet Functions	2	May 21st 06 04:35 PM
Print cell borders with special 'skin'	DavieM	Excel Discussion (Misc queries)	0	March 25th 06 10:52 PM
Cell borders and fill	Marietta	Excel Worksheet Functions	4	August 6th 05 04:29 PM
Using angled column headings and borders	Lisa H	Excel Discussion (Misc queries)	3	July 5th 05 04:30 PM

Menu

About Us