If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Change cell color based on another cell value
Hi All
I must be thick headed; I have looked through most of the posts here for help and some come very close to answering my question, or maybe it’s just me! I have the three conditional formatting settings in cell H1 based on the value of cell A1. Please note there is no value in H1, no number or letter, just the pattern color. I was hoping not to / do not want to use VB editor to create some master code for the work sheet. I just want to use a FORMULA in H1 to change the cell (H1) color based on the number value in cell A1. Like =IF(A1=5,".ColorIndex = 6",IF(A1=6,".ColorIndex = 7")) (Numbers 1 – 10 would each have a different color, 1-red, 2-blue, 3-green, and so on) Now for the thick headed part, if a formula can not change a cell color is there ANY other way to do this other than some code sheet, VB editing, monster in Excel 2003? Thanks for taking the time to look this over, and if I need to wrestle with the code monster will you all be there to guide me through, PLEASE? Dan |
#2
|
|||
|
|||
Change cell color based on another cell value
Dan
For 10 numbers you would have to use VBA......event code is best. Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Variant Set vRngInput = Intersect(Target, Range("A1")) If vRngInput Is Nothing Then Exit Sub For Each rng In vRngInput 'Determine the color Select Case rng.Value Case Is = 1: Num = 6 'yellow Case Is = 2: Num = 10 'green Case Is = 3: Num = 5 'blue Case Is = 4: Num = 3 'red Case Is = 5: Num = 46 'orange 'add more cases here as above End Select 'Apply the color Range("H1").Interior.ColorIndex = Num Next rng End Sub Just add more Cases and colors using the example above. To see what the colorindexes are visit David McRitchie's site http://www.mvps.org/dmcritchie/excel/colors.htm This code is sheet event code. Right-click on the sheet tab and "View Code" Copy/paste the code into that sheet module. You can add the Cases at that point. Gord Dibben MS Excel MVP On Wed, 14 Feb 2007 12:47:18 -0800, danielocope wrote: Hi All I must be thick headed; I have looked through most of the posts here for help and some come very close to answering my question, or maybe its just me! I have the three conditional formatting settings in cell H1 based on the value of cell A1. Please note there is no value in H1, no number or letter, just the pattern color. I was hoping not to / do not want to use VB editor to create some master code for the work sheet. I just want to use a FORMULA in H1 to change the cell (H1) color based on the number value in cell A1. Like =IF(A1=5,".ColorIndex = 6",IF(A1=6,".ColorIndex = 7")) (Numbers 1 10 would each have a different color, 1-red, 2-blue, 3-green, and so on) Now for the thick headed part, if a formula can not change a cell color is there ANY other way to do this other than some code sheet, VB editing, monster in Excel 2003? Thanks for taking the time to look this over, and if I need to wrestle with the code monster will you all be there to guide me through, PLEASE? Dan |
#3
|
|||
|
|||
Change cell color based on another cell value
Gord Dibben
MS Excel MVP Thank you Gord, that works great! And the best part is I did not need to fight the code monster. I just copied your code to the sheet tab as you explained and vole’ it worked! You would think that would have been the end of it correct? Wrong! The monster got a hold of me and now is dragging me down that slippery slope. If you would be willing to help me out and offer another suggestion I would be grateful? All on my own I figured out that if I change the last line in your code, Range("H1").Interior.ColorIndex = Num,- from “H1” to “A1:A40” (or A:A) I can get the whole column of cell colors to change based on the number in cell A1. OR If I change the SET line of your code “Set vRngInput = Intersect(Target, Range("A1"))” from “A1” to “A1:A40” or(“A:A”),- then the cell A1 color will change based on the last number entered in any cell in the column. AND Combining the two code changes noted above the whole column changes with each number interned in any cell in the column! NOW What if I need each individual cell (in the range of A1:A40) to change color based on the number within each individual cell. And I want 40 colors to pick from, let’s just use color #1 - #40, this is what I tried so far. By adding Case lines to the Case section of your code to add 40 colors like this- Case Is = 1: Num = 1 Case Is = 2: Num = 2 Case Is = 3: Num = 3 And so on all the way to 40, I can get all the colors to work fine. For the individual cells I thought if I changed the 2nd line of your code from “Dim rng As Range” to “Dim rng As each” that would allow each cell to have its own color. BUT as you know that did not work, in fact the monster won’t even let me make that change. I used CellFormat in place of “rng” and the monster made me change a bunch of other lines of code and before long nothing looked like the code you gave me so I just exited out and saved your original code in another work sheet. I copied the total code and pasting it 40 times, changing the “Range” in each set of codes to “A2, A3 and so on” but that only make the monster even angrier. So here I am fighting the code monster UUUGGG! Can you please suggest the proper changes to your code? Thank you for your support I know how frustrating it must be for you at times dealing with meatheads’ like me! Dan "Gord Dibben" wrote: Dan For 10 numbers you would have to use VBA......event code is best. Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Variant Set vRngInput = Intersect(Target, Range("A1")) If vRngInput Is Nothing Then Exit Sub For Each rng In vRngInput 'Determine the color Select Case rng.Value Case Is = 1: Num = 6 'yellow Case Is = 2: Num = 10 'green Case Is = 3: Num = 5 'blue Case Is = 4: Num = 3 'red Case Is = 5: Num = 46 'orange 'add more cases here as above End Select 'Apply the color Range("H1").Interior.ColorIndex = Num Next rng End Sub Just add more Cases and colors using the example above. To see what the colorindexes are visit David McRitchie's site http://www.mvps.org/dmcritchie/excel/colors.htm This code is sheet event code. Right-click on the sheet tab and "View Code" Copy/paste the code into that sheet module. You can add the Cases at that point. Gord Dibben MS Excel MVP On Wed, 14 Feb 2007 12:47:18 -0800, danielocope wrote: Hi All I must be thick headed; I have looked through most of the posts here for help and some come very close to answering my question, or maybe it’s just me! I have the three conditional formatting settings in cell H1 based on the value of cell A1. Please note there is no value in H1, no number or letter, just the pattern color. I was hoping not to / do not want to use VB editor to create some master code for the work sheet. I just want to use a FORMULA in H1 to change the cell (H1) color based on the number value in cell A1. Like =IF(A1=5,".ColorIndex = 6",IF(A1=6,".ColorIndex = 7")) (Numbers 1 – 10 would each have a different color, 1-red, 2-blue, 3-green, and so on) Now for the thick headed part, if a formula can not change a cell color is there ANY other way to do this other than some code sheet, VB editing, monster in Excel 2003? Thanks for taking the time to look this over, and if I need to wrestle with the code monster will you all be there to guide me through, PLEASE? Dan |
#4
|
|||
|
|||
Change cell color based on another cell value
To have each cell its own color depending upon value in the cell.........
Note the change to Target.Interior.ColorIndex = Num Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Variant Set vRngInput = Intersect(Target, Range("A1:A40")) If vRngInput Is Nothing Then Exit Sub For Each rng In vRngInput 'Determine the color Select Case rng.Value Case Is = 1: Num = 6 'yellow Case Is = 2: Num = 10 'green Case Is = 3: Num = 5 'blue Case Is = 4: Num = 3 'red Case Is = 5: Num = 46 'orange 'add more cases here as above End Select 'Apply the color Target.Interior.ColorIndex = Num Next rng End Sub For 40 colors and cases it may be easier this way........... Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range Dim rr As Range Set r = Range("A1:A40") If Intersect(Target, r) Is Nothing Then Exit Sub End If vals = Array("1", "2", "3", "4", "5", "6", "7", "8", "9") nums = Array(1, 2, 3, 4, 5, 6, 7, 8, 9) For Each rr In r icolor = 0 For i = LBound(vals) To UBound(vals) If UCase(rr.Value) = vals(i) Then icolor = nums(i) End If Next If icolor 0 Then rr.Interior.ColorIndex = icolor End If Next End Sub Gord On Thu, 15 Feb 2007 09:10:29 -0800, danielocope wrote: Gord Dibben MS Excel MVP Thank you Gord, that works great! And the best part is I did not need to fight the code monster. I just copied your code to the sheet tab as you explained and vole it worked! You would think that would have been the end of it correct? Wrong! The monster got a hold of me and now is dragging me down that slippery slope. If you would be willing to help me out and offer another suggestion I would be grateful? All on my own I figured out that if I change the last line in your code, Range("H1").Interior.ColorIndex = Num,- from H1 to A1:A40 (or A:A) I can get the whole column of cell colors to change based on the number in cell A1. OR If I change the SET line of your code Set vRngInput = Intersect(Target, Range("A1")) from A1 to A1:A40 or(A:A),- then the cell A1 color will change based on the last number entered in any cell in the column. AND Combining the two code changes noted above the whole column changes with each number interned in any cell in the column! NOW What if I need each individual cell (in the range of A1:A40) to change color based on the number within each individual cell. And I want 40 colors to pick from, lets just use color #1 - #40, this is what I tried so far. By adding Case lines to the Case section of your code to add 40 colors like this- Case Is = 1: Num = 1 Case Is = 2: Num = 2 Case Is = 3: Num = 3 And so on all the way to 40, I can get all the colors to work fine. For the individual cells I thought if I changed the 2nd line of your code from Dim rng As Range to Dim rng As each that would allow each cell to have its own color. BUT as you know that did not work, in fact the monster wont even let me make that change. I used CellFormat in place of rng and the monster made me change a bunch of other lines of code and before long nothing looked like the code you gave me so I just exited out and saved your original code in another work sheet. I copied the total code and pasting it 40 times, changing the Range in each set of codes to A2, A3 and so on but that only make the monster even angrier. So here I am fighting the code monster UUUGGG! Can you please suggest the proper changes to your code? Thank you for your support I know how frustrating it must be for you at times dealing with meatheads like me! Dan "Gord Dibben" wrote: Dan For 10 numbers you would have to use VBA......event code is best. Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Variant Set vRngInput = Intersect(Target, Range("A1")) If vRngInput Is Nothing Then Exit Sub For Each rng In vRngInput 'Determine the color Select Case rng.Value Case Is = 1: Num = 6 'yellow Case Is = 2: Num = 10 'green Case Is = 3: Num = 5 'blue Case Is = 4: Num = 3 'red Case Is = 5: Num = 46 'orange 'add more cases here as above End Select 'Apply the color Range("H1").Interior.ColorIndex = Num Next rng End Sub Just add more Cases and colors using the example above. To see what the colorindexes are visit David McRitchie's site http://www.mvps.org/dmcritchie/excel/colors.htm This code is sheet event code. Right-click on the sheet tab and "View Code" Copy/paste the code into that sheet module. You can add the Cases at that point. Gord Dibben MS Excel MVP On Wed, 14 Feb 2007 12:47:18 -0800, danielocope wrote: Hi All I must be thick headed; I have looked through most of the posts here for help and some come very close to answering my question, or maybe its just me! I have the three conditional formatting settings in cell H1 based on the value of cell A1. Please note there is no value in H1, no number or letter, just the pattern color. I was hoping not to / do not want to use VB editor to create some master code for the work sheet. I just want to use a FORMULA in H1 to change the cell (H1) color based on the number value in cell A1. Like =IF(A1=5,".ColorIndex = 6",IF(A1=6,".ColorIndex = 7")) (Numbers 1 10 would each have a different color, 1-red, 2-blue, 3-green, and so on) Now for the thick headed part, if a formula can not change a cell color is there ANY other way to do this other than some code sheet, VB editing, monster in Excel 2003? Thanks for taking the time to look this over, and if I need to wrestle with the code monster will you all be there to guide me through, PLEASE? Dan |
#5
|
|||
|
|||
Change cell color based on another cell value
Thank you Gord Dibben the two codes work great for me.
I will never understand it but it works THANK YOU VERY MUCH Dan "Gord Dibben" wrote: To have each cell its own color depending upon value in the cell......... Note the change to Target.Interior.ColorIndex = Num Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Variant Set vRngInput = Intersect(Target, Range("A1:A40")) If vRngInput Is Nothing Then Exit Sub For Each rng In vRngInput 'Determine the color Select Case rng.Value Case Is = 1: Num = 6 'yellow Case Is = 2: Num = 10 'green Case Is = 3: Num = 5 'blue Case Is = 4: Num = 3 'red Case Is = 5: Num = 46 'orange 'add more cases here as above End Select 'Apply the color Target.Interior.ColorIndex = Num Next rng End Sub For 40 colors and cases it may be easier this way........... Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range Dim rr As Range Set r = Range("A1:A40") If Intersect(Target, r) Is Nothing Then Exit Sub End If vals = Array("1", "2", "3", "4", "5", "6", "7", "8", "9") nums = Array(1, 2, 3, 4, 5, 6, 7, 8, 9) For Each rr In r icolor = 0 For i = LBound(vals) To UBound(vals) If UCase(rr.Value) = vals(i) Then icolor = nums(i) End If Next If icolor 0 Then rr.Interior.ColorIndex = icolor End If Next End Sub Gord On Thu, 15 Feb 2007 09:10:29 -0800, danielocope wrote: Gord Dibben MS Excel MVP Thank you Gord, that works great! And the best part is I did not need to fight the code monster. I just copied your code to the sheet tab as you explained and vole’ it worked! You would think that would have been the end of it correct? Wrong! The monster got a hold of me and now is dragging me down that slippery slope. If you would be willing to help me out and offer another suggestion I would be grateful? All on my own I figured out that if I change the last line in your code, Range("H1").Interior.ColorIndex = Num,- from “H1” to “A1:A40” (or A:A) I can get the whole column of cell colors to change based on the number in cell A1. OR If I change the SET line of your code “Set vRngInput = Intersect(Target, Range("A1"))” from “A1” to “A1:A40” or(“A:A”),- then the cell A1 color will change based on the last number entered in any cell in the column. AND Combining the two code changes noted above the whole column changes with each number interned in any cell in the column! NOW What if I need each individual cell (in the range of A1:A40) to change color based on the number within each individual cell. And I want 40 colors to pick from, let’s just use color #1 - #40, this is what I tried so far. By adding Case lines to the Case section of your code to add 40 colors like this- Case Is = 1: Num = 1 Case Is = 2: Num = 2 Case Is = 3: Num = 3 And so on all the way to 40, I can get all the colors to work fine. For the individual cells I thought if I changed the 2nd line of your code from “Dim rng As Range” to “Dim rng As each” that would allow each cell to have its own color. BUT as you know that did not work, in fact the monster won’t even let me make that change. I used CellFormat in place of “rng” and the monster made me change a bunch of other lines of code and before long nothing looked like the code you gave me so I just exited out and saved your original code in another work sheet. I copied the total code and pasting it 40 times, changing the “Range” in each set of codes to “A2, A3 and so on” but that only make the monster even angrier. So here I am fighting the code monster UUUGGG! Can you please suggest the proper changes to your code? Thank you for your support I know how frustrating it must be for you at times dealing with meatheads’ like me! Dan "Gord Dibben" wrote: Dan For 10 numbers you would have to use VBA......event code is best. Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Variant Set vRngInput = Intersect(Target, Range("A1")) If vRngInput Is Nothing Then Exit Sub For Each rng In vRngInput 'Determine the color Select Case rng.Value Case Is = 1: Num = 6 'yellow Case Is = 2: Num = 10 'green Case Is = 3: Num = 5 'blue Case Is = 4: Num = 3 'red Case Is = 5: Num = 46 'orange 'add more cases here as above End Select 'Apply the color Range("H1").Interior.ColorIndex = Num Next rng End Sub Just add more Cases and colors using the example above. To see what the colorindexes are visit David McRitchie's site http://www.mvps.org/dmcritchie/excel/colors.htm This code is sheet event code. Right-click on the sheet tab and "View Code" Copy/paste the code into that sheet module. You can add the Cases at that point. Gord Dibben MS Excel MVP On Wed, 14 Feb 2007 12:47:18 -0800, danielocope wrote: Hi All I must be thick headed; I have looked through most of the posts here for help and some come very close to answering my question, or maybe it’s just me! I have the three conditional formatting settings in cell H1 based on the value of cell A1. Please note there is no value in H1, no number or letter, just the pattern color. I was hoping not to / do not want to use VB editor to create some master code for the work sheet. I just want to use a FORMULA in H1 to change the cell (H1) color based on the number value in cell A1. Like =IF(A1=5,".ColorIndex = 6",IF(A1=6,".ColorIndex = 7")) (Numbers 1 – 10 would each have a different color, 1-red, 2-blue, 3-green, and so on) Now for the thick headed part, if a formula can not change a cell color is there ANY other way to do this other than some code sheet, VB editing, monster in Excel 2003? Thanks for taking the time to look this over, and if I need to wrestle with the code monster will you all be there to guide me through, PLEASE? Dan |
#6
|
|||
|
|||
Change cell color based on another cell value
Dan
I just barely understand it so welcome to the clubg I like the second one that uses the Arrays. Not so much typing. One of the real coders can probably come up with a method of building an array without typing all those numbers. Gord On Thu, 15 Feb 2007 14:05:05 -0800, danielocope wrote: Thank you Gord Dibben the two codes work great for me. I will never understand it but it works THANK YOU VERY MUCH Dan "Gord Dibben" wrote: To have each cell its own color depending upon value in the cell......... Note the change to Target.Interior.ColorIndex = Num Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Variant Set vRngInput = Intersect(Target, Range("A1:A40")) If vRngInput Is Nothing Then Exit Sub For Each rng In vRngInput 'Determine the color Select Case rng.Value Case Is = 1: Num = 6 'yellow Case Is = 2: Num = 10 'green Case Is = 3: Num = 5 'blue Case Is = 4: Num = 3 'red Case Is = 5: Num = 46 'orange 'add more cases here as above End Select 'Apply the color Target.Interior.ColorIndex = Num Next rng End Sub For 40 colors and cases it may be easier this way........... Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range Dim rr As Range Set r = Range("A1:A40") If Intersect(Target, r) Is Nothing Then Exit Sub End If vals = Array("1", "2", "3", "4", "5", "6", "7", "8", "9") nums = Array(1, 2, 3, 4, 5, 6, 7, 8, 9) For Each rr In r icolor = 0 For i = LBound(vals) To UBound(vals) If UCase(rr.Value) = vals(i) Then icolor = nums(i) End If Next If icolor 0 Then rr.Interior.ColorIndex = icolor End If Next End Sub Gord On Thu, 15 Feb 2007 09:10:29 -0800, danielocope wrote: Gord Dibben MS Excel MVP Thank you Gord, that works great! And the best part is I did not need to fight the code monster. I just copied your code to the sheet tab as you explained and vole it worked! You would think that would have been the end of it correct? Wrong! The monster got a hold of me and now is dragging me down that slippery slope. If you would be willing to help me out and offer another suggestion I would be grateful? All on my own I figured out that if I change the last line in your code, Range("H1").Interior.ColorIndex = Num,- from H1 to A1:A40 (or A:A) I can get the whole column of cell colors to change based on the number in cell A1. OR If I change the SET line of your code Set vRngInput = Intersect(Target, Range("A1")) from A1 to A1:A40 or(A:A),- then the cell A1 color will change based on the last number entered in any cell in the column. AND Combining the two code changes noted above the whole column changes with each number interned in any cell in the column! NOW What if I need each individual cell (in the range of A1:A40) to change color based on the number within each individual cell. And I want 40 colors to pick from, lets just use color #1 - #40, this is what I tried so far. By adding Case lines to the Case section of your code to add 40 colors like this- Case Is = 1: Num = 1 Case Is = 2: Num = 2 Case Is = 3: Num = 3 And so on all the way to 40, I can get all the colors to work fine. For the individual cells I thought if I changed the 2nd line of your code from Dim rng As Range to Dim rng As each that would allow each cell to have its own color. BUT as you know that did not work, in fact the monster wont even let me make that change. I used CellFormat in place of rng and the monster made me change a bunch of other lines of code and before long nothing looked like the code you gave me so I just exited out and saved your original code in another work sheet. I copied the total code and pasting it 40 times, changing the Range in each set of codes to A2, A3 and so on but that only make the monster even angrier. So here I am fighting the code monster UUUGGG! Can you please suggest the proper changes to your code? Thank you for your support I know how frustrating it must be for you at times dealing with meatheads like me! Dan "Gord Dibben" wrote: Dan For 10 numbers you would have to use VBA......event code is best. Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Variant Set vRngInput = Intersect(Target, Range("A1")) If vRngInput Is Nothing Then Exit Sub For Each rng In vRngInput 'Determine the color Select Case rng.Value Case Is = 1: Num = 6 'yellow Case Is = 2: Num = 10 'green Case Is = 3: Num = 5 'blue Case Is = 4: Num = 3 'red Case Is = 5: Num = 46 'orange 'add more cases here as above End Select 'Apply the color Range("H1").Interior.ColorIndex = Num Next rng End Sub Just add more Cases and colors using the example above. To see what the colorindexes are visit David McRitchie's site http://www.mvps.org/dmcritchie/excel/colors.htm This code is sheet event code. Right-click on the sheet tab and "View Code" Copy/paste the code into that sheet module. You can add the Cases at that point. Gord Dibben MS Excel MVP On Wed, 14 Feb 2007 12:47:18 -0800, danielocope wrote: Hi All I must be thick headed; I have looked through most of the posts here for help and some come very close to answering my question, or maybe its just me! I have the three conditional formatting settings in cell H1 based on the value of cell A1. Please note there is no value in H1, no number or letter, just the pattern color. I was hoping not to / do not want to use VB editor to create some master code for the work sheet. I just want to use a FORMULA in H1 to change the cell (H1) color based on the number value in cell A1. Like =IF(A1=5,".ColorIndex = 6",IF(A1=6,".ColorIndex = 7")) (Numbers 1 10 would each have a different color, 1-red, 2-blue, 3-green, and so on) Now for the thick headed part, if a formula can not change a cell color is there ANY other way to do this other than some code sheet, VB editing, monster in Excel 2003? Thanks for taking the time to look this over, and if I need to wrestle with the code monster will you all be there to guide me through, PLEASE? Dan |
#7
|
|||
|
|||
ffswfsf
Hello i have entered the code exactly as you say and it
doesnt do anything, im not sure what im doing wrong. Gord Dibben wrote: DanFor 10 numbers you would have to use VBA......event code is best. 14-Feb-07 Dan For 10 numbers you would have to use VBA......event code is best. Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Variant Set vRngInput = Intersect(Target, Range("A1")) If vRngInput Is Nothing Then Exit Sub For Each rng In vRngInput 'Determine the color Select Case rng.Value Case Is = 1: Num = 6 'yellow Case Is = 2: Num = 10 'green Case Is = 3: Num = 5 'blue Case Is = 4: Num = 3 'red Case Is = 5: Num = 46 'orange 'add more cases here as above End Select 'Apply the color Range("H1").Interior.ColorIndex = Num Next rng End Sub Just add more Cases and colors using the example above. To see what the colorindexes are visit David McRitchie's site http://www.mvps.org/dmcritchie/excel/colors.htm This code is sheet event code. Right-click on the sheet tab and "View Code" Copy/paste the code into that sheet module. You can add the Cases at that point. Gord Dibben MS Excel MVP On Wed, 14 Feb 2007 12:47:18 -0800, danielocope wrote: Previous Posts In This Thread: On Wednesday, February 14, 2007 3:47 PM danielocop wrote: Change cell color based on another cell value Hi All I must be thick headed; I have looked through most of the posts here for help and some come very close to answering my question, or maybe it???s just me! I have the three conditional formatting settings in cell H1 based on the value of cell A1. Please note there is no value in H1, no number or letter, just the pattern color. I was hoping not to / do not want to use VB editor to create some master code for the work sheet. I just want to use a FORMULA in H1 to change the cell (H1) color based on the number value in cell A1. Like =IF(A1=5,".ColorIndex = 6",IF(A1=6,".ColorIndex = 7")) (Numbers 1 ??? 10 would each have a different color, 1-red, 2-blue, 3-green, and so on) Now for the thick headed part, if a formula can not change a cell color is there ANY other way to do this other than some code sheet, VB editing, monster in Excel 2003? Thanks for taking the time to look this over, and if I need to wrestle with the code monster will you all be there to guide me through, PLEASE? Dan On Wednesday, February 14, 2007 4:57 PM Gord Dibben wrote: DanFor 10 numbers you would have to use VBA......event code is best. Dan For 10 numbers you would have to use VBA......event code is best. Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Variant Set vRngInput = Intersect(Target, Range("A1")) If vRngInput Is Nothing Then Exit Sub For Each rng In vRngInput 'Determine the color Select Case rng.Value Case Is = 1: Num = 6 'yellow Case Is = 2: Num = 10 'green Case Is = 3: Num = 5 'blue Case Is = 4: Num = 3 'red Case Is = 5: Num = 46 'orange 'add more cases here as above End Select 'Apply the color Range("H1").Interior.ColorIndex = Num Next rng End Sub Just add more Cases and colors using the example above. To see what the colorindexes are visit David McRitchie's site http://www.mvps.org/dmcritchie/excel/colors.htm This code is sheet event code. Right-click on the sheet tab and "View Code" Copy/paste the code into that sheet module. You can add the Cases at that point. Gord Dibben MS Excel MVP On Wed, 14 Feb 2007 12:47:18 -0800, danielocope wrote: On Thursday, February 15, 2007 12:10 PM danielocop wrote: Gord Dibben MS Excel MVPThank you Gord, that works great! Gord Dibben MS Excel MVP Thank you Gord, that works great! And the best part is I did not need to fight the code monster. I just copied your code to the sheet tab as you explained and vole??? it worked! You would think that would have been the end of it correct? Wrong! The monster got a hold of me and now is dragging me down that slippery slope. If you would be willing to help me out and offer another suggestion I would be grateful? All on my own I figured out that if I change the last line in your code, Range("H1").Interior.ColorIndex = Num,- from ???H1??? to ???A1:A40??? (or A:A) I can get the whole column of cell colors to change based on the number in cell A1. OR If I change the SET line of your code ???Set vRngInput = Intersect(Target, Range("A1"))??? from ???A1??? to ???A1:A40??? or(???A:A???),- then the cell A1 color will change based on the last number entered in any cell in the column. AND Combining the two code changes noted above the whole column changes with each number interned in any cell in the column! NOW What if I need each individual cell (in the range of A1:A40) to change color based on the number within each individual cell. And I want 40 colors to pick from, let???s just use color #1 - #40, this is what I tried so far. By adding Case lines to the Case section of your code to add 40 colors like this- Case Is = 1: Num = 1 Case Is = 2: Num = 2 Case Is = 3: Num = 3 And so on all the way to 40, I can get all the colors to work fine. For the individual cells I thought if I changed the 2nd line of your code from ???Dim rng As Range??? to ???Dim rng As each??? that would allow each cell to have its own color. BUT as you know that did not work, in fact the monster won???t even let me make that change. I used CellFormat in place of ???rng??? and the monster made me change a bunch of other lines of code and before long nothing looked like the code you gave me so I just exited out and saved your original code in another work sheet. I copied the total code and pasting it 40 times, changing the ???Range??? in each set of codes to ???A2, A3 and so on??? but that only make the monster even angrier. So here I am fighting the code monster UUUGGG! Can you please suggest the proper changes to your code? Thank you for your support I know how frustrating it must be for you at times dealing with meatheads??? like me! Dan "Gord Dibben" wrote: On Thursday, February 15, 2007 1:51 PM Gord Dibben wrote: To have each cell its own color depending upon value in the cell......... To have each cell its own color depending upon value in the cell......... Note the change to Target.Interior.ColorIndex = Num Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Variant Set vRngInput = Intersect(Target, Range("A1:A40")) If vRngInput Is Nothing Then Exit Sub For Each rng In vRngInput 'Determine the color Select Case rng.Value Case Is = 1: Num = 6 'yellow Case Is = 2: Num = 10 'green Case Is = 3: Num = 5 'blue Case Is = 4: Num = 3 'red Case Is = 5: Num = 46 'orange 'add more cases here as above End Select 'Apply the color Target.Interior.ColorIndex = Num Next rng End Sub For 40 colors and cases it may be easier this way........... Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range Dim rr As Range Set r = Range("A1:A40") If Intersect(Target, r) Is Nothing Then Exit Sub End If vals = Array("1", "2", "3", "4", "5", "6", "7", "8", "9") nums = Array(1, 2, 3, 4, 5, 6, 7, 8, 9) For Each rr In r icolor = 0 For i = LBound(vals) To UBound(vals) If UCase(rr.Value) = vals(i) Then icolor = nums(i) End If Next If icolor 0 Then rr.Interior.ColorIndex = icolor End If Next End Sub Gord On Thu, 15 Feb 2007 09:10:29 -0800, danielocope wrote: On Thursday, February 15, 2007 5:05 PM danielocop wrote: Thank you Gord Dibben the two codes work great for me. Thank you Gord Dibben the two codes work great for me. I will never understand it but it works THANK YOU VERY MUCH Dan "Gord Dibben" wrote: On Thursday, February 15, 2007 6:15 PM Gord Dibben wrote: DanI just barely understand it so welcome to the clubgI like the second one Dan I just barely understand it so welcome to the clubg I like the second one that uses the Arrays. Not so much typing. One of the real coders can probably come up with a method of building an array without typing all those numbers. Gord On Thu, 15 Feb 2007 14:05:05 -0800, danielocope wrote: Submitted via EggHeadCafe - Software Developer Portal of Choice Server Side Processing in ADO.NET/WCF Data Services http://www.eggheadcafe.com/tutorials...essing-in.aspx |
#9
|
|||
|
|||
ffswfsf
Why don't you start by describing your specific needs rather than tack onto
someone else's thread? There are two sets of code posted in this thread. Which set did you use and why did you pick that particular code? The code may not be helpful depending upon your situation. Gord On Thu, 27 May 2010 06:31:35 -0700, Joe Bell wrote: Hello i have entered the code exactly as you say and it doesnt do anything, im not sure what im doing wrong. Gord Dibben wrote: DanFor 10 numbers you would have to use VBA......event code is best. 14-Feb-07 Dan For 10 numbers you would have to use VBA......event code is best. Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Variant Set vRngInput = Intersect(Target, Range("A1")) If vRngInput Is Nothing Then Exit Sub For Each rng In vRngInput 'Determine the color Select Case rng.Value Case Is = 1: Num = 6 'yellow Case Is = 2: Num = 10 'green Case Is = 3: Num = 5 'blue Case Is = 4: Num = 3 'red Case Is = 5: Num = 46 'orange 'add more cases here as above End Select 'Apply the color Range("H1").Interior.ColorIndex = Num Next rng End Sub Just add more Cases and colors using the example above. To see what the colorindexes are visit David McRitchie's site http://www.mvps.org/dmcritchie/excel/colors.htm This code is sheet event code. Right-click on the sheet tab and "View Code" Copy/paste the code into that sheet module. You can add the Cases at that point. Gord Dibben MS Excel MVP On Wed, 14 Feb 2007 12:47:18 -0800, danielocope wrote: Previous Posts In This Thread: On Wednesday, February 14, 2007 3:47 PM danielocop wrote: Change cell color based on another cell value Hi All I must be thick headed; I have looked through most of the posts here for help and some come very close to answering my question, or maybe it???s just me! I have the three conditional formatting settings in cell H1 based on the value of cell A1. Please note there is no value in H1, no number or letter, just the pattern color. I was hoping not to / do not want to use VB editor to create some master code for the work sheet. I just want to use a FORMULA in H1 to change the cell (H1) color based on the number value in cell A1. Like =IF(A1=5,".ColorIndex = 6",IF(A1=6,".ColorIndex = 7")) (Numbers 1 ??? 10 would each have a different color, 1-red, 2-blue, 3-green, and so on) Now for the thick headed part, if a formula can not change a cell color is there ANY other way to do this other than some code sheet, VB editing, monster in Excel 2003? Thanks for taking the time to look this over, and if I need to wrestle with the code monster will you all be there to guide me through, PLEASE? Dan On Wednesday, February 14, 2007 4:57 PM Gord Dibben wrote: DanFor 10 numbers you would have to use VBA......event code is best. Dan For 10 numbers you would have to use VBA......event code is best. Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Variant Set vRngInput = Intersect(Target, Range("A1")) If vRngInput Is Nothing Then Exit Sub For Each rng In vRngInput 'Determine the color Select Case rng.Value Case Is = 1: Num = 6 'yellow Case Is = 2: Num = 10 'green Case Is = 3: Num = 5 'blue Case Is = 4: Num = 3 'red Case Is = 5: Num = 46 'orange 'add more cases here as above End Select 'Apply the color Range("H1").Interior.ColorIndex = Num Next rng End Sub Just add more Cases and colors using the example above. To see what the colorindexes are visit David McRitchie's site http://www.mvps.org/dmcritchie/excel/colors.htm This code is sheet event code. Right-click on the sheet tab and "View Code" Copy/paste the code into that sheet module. You can add the Cases at that point. Gord Dibben MS Excel MVP On Wed, 14 Feb 2007 12:47:18 -0800, danielocope wrote: On Thursday, February 15, 2007 12:10 PM danielocop wrote: Gord Dibben MS Excel MVPThank you Gord, that works great! Gord Dibben MS Excel MVP Thank you Gord, that works great! And the best part is I did not need to fight the code monster. I just copied your code to the sheet tab as you explained and vole??? it worked! You would think that would have been the end of it correct? Wrong! The monster got a hold of me and now is dragging me down that slippery slope. If you would be willing to help me out and offer another suggestion I would be grateful? All on my own I figured out that if I change the last line in your code, Range("H1").Interior.ColorIndex = Num,- from ???H1??? to ???A1:A40??? (or A:A) I can get the whole column of cell colors to change based on the number in cell A1. OR If I change the SET line of your code ???Set vRngInput = Intersect(Target, Range("A1"))??? from ???A1??? to ???A1:A40??? or(???A:A???),- then the cell A1 color will change based on the last number entered in any cell in the column. AND Combining the two code changes noted above the whole column changes with each number interned in any cell in the column! NOW What if I need each individual cell (in the range of A1:A40) to change color based on the number within each individual cell. And I want 40 colors to pick from, let???s just use color #1 - #40, this is what I tried so far. By adding Case lines to the Case section of your code to add 40 colors like this- Case Is = 1: Num = 1 Case Is = 2: Num = 2 Case Is = 3: Num = 3 And so on all the way to 40, I can get all the colors to work fine. For the individual cells I thought if I changed the 2nd line of your code from ???Dim rng As Range??? to ???Dim rng As each??? that would allow each cell to have its own color. BUT as you know that did not work, in fact the monster won???t even let me make that change. I used CellFormat in place of ???rng??? and the monster made me change a bunch of other lines of code and before long nothing looked like the code you gave me so I just exited out and saved your original code in another work sheet. I copied the total code and pasting it 40 times, changing the ???Range??? in each set of codes to ???A2, A3 and so on??? but that only make the monster even angrier. So here I am fighting the code monster UUUGGG! Can you please suggest the proper changes to your code? Thank you for your support I know how frustrating it must be for you at times dealing with meatheads??? like me! Dan "Gord Dibben" wrote: On Thursday, February 15, 2007 1:51 PM Gord Dibben wrote: To have each cell its own color depending upon value in the cell......... To have each cell its own color depending upon value in the cell......... Note the change to Target.Interior.ColorIndex = Num Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Variant Set vRngInput = Intersect(Target, Range("A1:A40")) If vRngInput Is Nothing Then Exit Sub For Each rng In vRngInput 'Determine the color Select Case rng.Value Case Is = 1: Num = 6 'yellow Case Is = 2: Num = 10 'green Case Is = 3: Num = 5 'blue Case Is = 4: Num = 3 'red Case Is = 5: Num = 46 'orange 'add more cases here as above End Select 'Apply the color Target.Interior.ColorIndex = Num Next rng End Sub For 40 colors and cases it may be easier this way........... Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range Dim rr As Range Set r = Range("A1:A40") If Intersect(Target, r) Is Nothing Then Exit Sub End If vals = Array("1", "2", "3", "4", "5", "6", "7", "8", "9") nums = Array(1, 2, 3, 4, 5, 6, 7, 8, 9) For Each rr In r icolor = 0 For i = LBound(vals) To UBound(vals) If UCase(rr.Value) = vals(i) Then icolor = nums(i) End If Next If icolor 0 Then rr.Interior.ColorIndex = icolor End If Next End Sub Gord On Thu, 15 Feb 2007 09:10:29 -0800, danielocope wrote: On Thursday, February 15, 2007 5:05 PM danielocop wrote: Thank you Gord Dibben the two codes work great for me. Thank you Gord Dibben the two codes work great for me. I will never understand it but it works THANK YOU VERY MUCH Dan "Gord Dibben" wrote: On Thursday, February 15, 2007 6:15 PM Gord Dibben wrote: DanI just barely understand it so welcome to the clubgI like the second one Dan I just barely understand it so welcome to the clubg I like the second one that uses the Arrays. Not so much typing. One of the real coders can probably come up with a method of building an array without typing all those numbers. Gord On Thu, 15 Feb 2007 14:05:05 -0800, danielocope wrote: Submitted via EggHeadCafe - Software Developer Portal of Choice Server Side Processing in ADO.NET/WCF Data Services http://www.eggheadcafe.com/tutorials...essing-in.aspx |
Thread Tools | |
Display Modes | |
|
|