Thanks for the answer,

Yep,this works. When i uncheck the checkbox, the right cell next to colored
cells isn't colored.
How can i uncheck this checkbox, or the underlying setting in Excel, using
VBA-code?

My other questions:

2) How do I change the formatting of a cel by VBA-code? For example: change
from General to Text
3)
Dim WeekdayNames(0 To 6) As String
Dim TempRange As Range
Set TempRange = ThisWorkbook.Worksheets(1).Range("A1:G1")
TempRange.Value = WeekdayNames

the last statement automatically fills a range with the array-values.
Can i use a similar approach to assign colors from an array of Long to
the background of a certain Range?

Like this:
Dim WeekColors(0 To 6) As Long

Colors(0) = RGB(0,255,255)
... = .........
Colors(6) = RGB(255,128, 0)

TempRange.Interior.Color = WeekColors
' but the last statement does not work. the whole background is
colored with the first color in the array Colors
' i want that the background of cell 0 in the TempRange is colored
with Colors(0), cell 1 with Colors(1), etc....

kind regards,

Johan De Schutter
Software designer

mailto:

Nisus nv
Antwerpsesteenweg 107, B-2390 Oostmalle
Tel: +32(0)3/312.92.30

Please visit our website: http://www.nisus.be

------------------------

"J.E. McGimpsey" wrote in message
...
Try unchecking the Tools/Options/Edit/Extend lists and formulas
checkbox.

In article ,
"Johan De Schutter" <jdsATDOTnisusDOTbe wrote:


When I click on the button the code is executed without any errors.
The code writes the name of the day in the cells A1 till G1 and it
colors
the cells with the workdays (monday till friday) as red and
the cells with the days of the weekend (saturday and sunday) as blue. No
problem until so far.

!!! But when I select cell H1 and a enter a character (for example 'm'
or
'j') cell H1 is also colored red.
And when I select cell I1 and a enter a character, cell I1 is also
colored
red.
And when I select J1 and enter a value, J1 is colored red..... and so
on.

When I enter a number into cell H1, nothing happens, the color of H1
does
not change.
Then I select I1 and enter a character, nothing happens, the color of I1
does not change.

It looks like there happens some kind of auto-fill for the colors of the
cells.