Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fill background color automatically by typing a letter in a cell | Excel Discussion (Misc queries) | |||
Changing Cell Background Color Depending on the Number | Excel Discussion (Misc queries) | |||
changing cell background color | Excel Worksheet Functions | |||
Changing background color based on different cell | Excel Discussion (Misc queries) | |||
Changing the Cell Background Color when a keyword is entered | Excel Programming |