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.

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" <[email protected]> wrote in message
news:[email protected]...
> 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.

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" <[email protected]> wrote in message
news:[email protected]...
> 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.

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> >
> >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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.
>>
>>
>>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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.
> >>
> >>
> >>
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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.
>> >>
>> >>
>> >>
>>
>>
>>