automatically changing the background color of a cell
 

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.

automatically changing the background color of a cell
 

Hi Martin

if you have only one criteria (or up to 3), choose the range b2:p2, choose
format / conditional formatting
choose
formula is
in the white line type
=$A$2=15
click on the format button, go to the patterns tab and choose the background
you want
click OK twice
(use the ADD button to add other criteria)

however, if you have more than 3 criteria or you really want to do it
programatically then here is an example of worksheet_change code that
demonstrates 9 different values in cell A2 and associated fill colour in the
range B2:P2

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range("A2")) Is Nothing Then
With Target
Select Case .Value
Case 1: Range("B2:P2").Interior.ColorIndex = 4
Case 2: Range("B2:P2").Interior.ColorIndex = 3
Case 3: Range("B2:P2").Interior.ColorIndex = 0
Case 4: Range("B2:P2").Interior.ColorIndex = 6
Case 5: Range("B2:P2").Interior.ColorIndex = 13
Case 6: Range("B2:P2").Interior.ColorIndex = 46
Case 7: Range("B2:P2").Interior.ColorIndex = 11
Case 8: Range("B2:P2").Interior.ColorIndex = 7
Case 9: Range("B2:P2").Interior.ColorIndex = 55
End Select
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

---
Please let us know if you need instructions on how to change or use this
code.

Cheers
JulieD


"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.

automatically changing the background color of a cell
 

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.

automatically changing the background color of a cell
 

If you want to avoid building the code, there is a free add-in to do it for
you at http://www.xldynamic.com/source/xld.....Download.html

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Sharad Naik" wrote in message
...
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.

automatically changing the background color of a cell
 

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.

automatically changing the background color of a cell
 

The program for conditional formatting doesnt help me, sorry.

"Bob Phillips" wrote:

If you want to avoid building the code, there is a free add-in to do it for
you at http://www.xldynamic.com/source/xld.....Download.html

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Sharad Naik" wrote in message
...
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.

automatically changing the background color of a cell
 

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.

automatically changing the background color of a cell
 

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.

automatically changing the background color of a cell
 

OK, I missed that the cell D2 had formula, I just change it's value manually
to try and it worked.

Anyway, so the manual change will be in column B or C .
So remove earlier code and copy the code below:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim nRow As Integer, nCols As Integer
If Not Application.Intersect(Target, Me.Range("B2:C100")) Is Nothing Then
nRow = Target.Row
Set Target = Me.Range("D" & nRow)
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
nCols = .Value
If nCols 252 Then nRow = 252 'so the last column not to exceed 256
Me.Range(.Offset(0, 1), .Offset(0, nCols)) _
.Interior.Color = 10079487 'assign color
End With
End If
End Sub

Sharad

"martin" wrote in message
...
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.