Dear Sharad,

Another small problem. As you may already noticed The number which is used
as a subjec to change the color of cells comes from a formula:
=ROUND((DAYS360(TODAY();B2)/30);0)
Now, if you change the end date (b2), the value changes, but the colors of
cell doesnt change. It chanegs when you clock on the formula bar
(=ROUND((DAYS360(TODAY();B2)/30);0)) and hit enter.
In other workds, seems that it automatically doesnt check the value and
change the cell color accordingly.
Also, everything works when you enter a value on the formula bar, and hit
enter.

Thanks.
"Sharad Naik" wrote:

I downloaded your excel file and as it is, found that, it working for the
Cell D2.

To make it work for D2:D100 use below code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Me.Range("D2:D100")) Is Nothing Then
If Not IsNumeric(Target.Value) Then Exit Sub
With Target
.EntireRow.Interior.Color = 16777215 'first clear all cells color
If .Value < 1 Then Exit Sub
Me.Range(.Offset(0, 1), .Offset(0, .Value)) _
.Interior.Color = 10079487 'assign color
End With
End If
End Sub



"martin" wrote in message
...
Thanks for replying that quickly.
Still, i am not able to make it work. Perhaps the excel file can help
itself. You are able to download it from:
http://www.zone.ee/munand/test.xls
From the file you can see the exact nature of the work. Is it possible to
make the code for d2:d100 in order that it would apply for the rest of the
rows.

Thanks.

"Sharad Naik" wrote:

Hi Martin ,
You can do it in the Sheet's Worksheet_Change event.
Copy the below code in the Sheet's worksheet_change event.
Then try entering different values in Cell A2 and see.
(you can also enter "0" to remove background colour of entire row.)
in the line commented with 'assign color below you can chose a
different color, you like.

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address = "$A$2" Then
.EntireRow.Interior.Color = 16777215 'first clear all cells color
If .Value < 1 Then Exit Sub
Me.Range(.Offset(0, 1), .Offset(0, .Value)) _
.Interior.Color = 10079487 'assign color
End If
End With
End Sub

Sharad

"martin" wrote in message
...
Dear all,

I need Excel to automatically change the background color of a cell.
The
problem is as follows:
I have a number, say 15 in A2. I need Excel to change the background
color
of b2:p2, as this is 15 cells on the right from that.

Is that possible,
All the best,
and thanks,
Martin.