| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
SHADING FOR MULTIPLE TEXT ENTRIES
hello again,
more specifics on exactly what i am trying to do. i have 300 rows (record entries) containing people's names. These folks over the next 12 months are going to be going in a variety of locations. I have a large amount of columns set up as dates. I am going to limit the data entry into the date column/cell to the following: DB, DN, DS, DO, DJ, HH, PCS, LV As I am going to do a bunch of counting formulas for each day (so we can figure out the number of folks in each location), so I need to make the text entry into each cell. What I do not know how to do (since conditional formating only allows 3 conditions)...is this: In a cell, I enter "DB". This entry triggers the cell to fill with a certain color shade, as well as change the entered text to the same color. Here is the color scheme that I would like to use: DB (gray 40%), DN (bright green), DS (sea green), DO (turqoise), DJ (red), HH (yellow), PCS (red), LV (black). appreciate any help that anyone can provide!!! thanks in advance :)) |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
SHADING FOR MULTIPLE TEXT ENTRIES
There's actually more to the puzzle than you've told us: how/when do the
colors in these cells return to normal?? Also, it would be a good thing to know the column IDs involved so that we could be more precise in our testing of the cells -- but the code below provides the basics. Read carefully - this code must go into the worksheet code area for the sheet where you are going to be typing in the "DB, "DJ, "PCS" etc entries. To get to the proper place, open your workbook, select that sheet and right-click on the sheet's name tab and choose [View Code] from the list that appears. Copy the code below and paste it into the empty code module that appeared when you clicked [View Code]. Close the VBA editor and give it a tryout. Private Sub Worksheet_Change(ByVal Target As Range) Const colorGray40 = 48 Const colorRed = 3 Const colorBlack = 1 Const colorSeaGreen = 50 Const colorBrightGreen = 4 Const colorTurquoise = 8 Const colorYellow = 6 If Target.Cells.Count 1 Then Exit Sub End If Select Case UCase(Trim(Target)) Case Is = "DB" Target.Interior.ColorIndex = colorGray40 Target.Font.ColorIndex = colorGray40 Case Is = "DN" Target.Interior.ColorIndex = colorBrightGreen Target.Font.ColorIndex = colorBrightGreen Case Is = "DS" Target.Interior.ColorIndex = colorSeaGreen Target.Font.ColorIndex = colorSeaGreen Case Is = "DO" Target.Interior.ColorIndex = colorTurquoise Target.Font.ColorIndex = colorTurquoise Case Is = "DJ" Target.Interior.ColorIndex = colorRed Target.Font.ColorIndex = colorRed Case Is = "HH" Target.Interior.ColorIndex = colorYellow Target.Font.ColorIndex = colorYellow Case Is = "PCS" Target.Interior.ColorIndex = colorRed Target.Font.ColorIndex = colorRed Case Is = "LV" Target.Interior.ColorIndex = 1 Target.Font.ColorIndex = 1 Case Else 'do nothing End Select End Sub Hope this helps. "shekpatrick" wrote: hello again, more specifics on exactly what i am trying to do. i have 300 rows (record entries) containing people's names. These folks over the next 12 months are going to be going in a variety of locations. I have a large amount of columns set up as dates. I am going to limit the data entry into the date column/cell to the following: DB, DN, DS, DO, DJ, HH, PCS, LV As I am going to do a bunch of counting formulas for each day (so we can figure out the number of folks in each location), so I need to make the text entry into each cell. What I do not know how to do (since conditional formating only allows 3 conditions)...is this: In a cell, I enter "DB". This entry triggers the cell to fill with a certain color shade, as well as change the entered text to the same color. Here is the color scheme that I would like to use: DB (gray 40%), DN (bright green), DS (sea green), DO (turqoise), DJ (red), HH (yellow), PCS (red), LV (black). appreciate any help that anyone can provide!!! thanks in advance :)) |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
SHADING FOR MULTIPLE TEXT ENTRIES
Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range Dim iColor As Long Set R = Range("A1:A100") 'adjust to suit If Intersect(Target, R) Is Nothing Or Target.Count 1 Then Exit Sub Vals = Array("DB", "DN", "DS", "DO", "DJ", "HH", "PCS", "LV") Nums = Array(48, 4, 50, 8, 3, 6, 3, 1) For i = LBound(Vals) To UBound(Vals) If UCase(Target.Value) = Vals(i) Then iColor = Nums(i) Next With Target .Interior.ColorIndex = iColor End With End Sub Gord Dibben MS Excel MVP On Thu, 18 Dec 2008 09:46:03 -0800, shekpatrick wrote: hello again, more specifics on exactly what i am trying to do. i have 300 rows (record entries) containing people's names. These folks over the next 12 months are going to be going in a variety of locations. I have a large amount of columns set up as dates. I am going to limit the data entry into the date column/cell to the following: DB, DN, DS, DO, DJ, HH, PCS, LV As I am going to do a bunch of counting formulas for each day (so we can figure out the number of folks in each location), so I need to make the text entry into each cell. What I do not know how to do (since conditional formating only allows 3 conditions)...is this: In a cell, I enter "DB". This entry triggers the cell to fill with a certain color shade, as well as change the entered text to the same color. Here is the color scheme that I would like to use: DB (gray 40%), DN (bright green), DS (sea green), DO (turqoise), DJ (red), HH (yellow), PCS (red), LV (black). appreciate any help that anyone can provide!!! thanks in advance :)) |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
SHADING FOR MULTIPLE TEXT ENTRIES
Mr Latham,
Your code was a huge help!! I did some tweaking, following your example...and now i understand the question that you posed, "how/when do the colors in these cells return to normal". I figured out how to return the interior color back to white when there is no entry in the cell. Once again, thank you very much!!! Patrick "JLatham" wrote: There's actually more to the puzzle than you've told us: how/when do the colors in these cells return to normal?? Also, it would be a good thing to know the column IDs involved so that we could be more precise in our testing of the cells -- but the code below provides the basics. Read carefully - this code must go into the worksheet code area for the sheet where you are going to be typing in the "DB, "DJ, "PCS" etc entries. To get to the proper place, open your workbook, select that sheet and right-click on the sheet's name tab and choose [View Code] from the list that appears. Copy the code below and paste it into the empty code module that appeared when you clicked [View Code]. Close the VBA editor and give it a tryout. Private Sub Worksheet_Change(ByVal Target As Range) Const colorGray40 = 48 Const colorRed = 3 Const colorBlack = 1 Const colorSeaGreen = 50 Const colorBrightGreen = 4 Const colorTurquoise = 8 Const colorYellow = 6 If Target.Cells.Count 1 Then Exit Sub End If Select Case UCase(Trim(Target)) Case Is = "DB" Target.Interior.ColorIndex = colorGray40 Target.Font.ColorIndex = colorGray40 Case Is = "DN" Target.Interior.ColorIndex = colorBrightGreen Target.Font.ColorIndex = colorBrightGreen Case Is = "DS" Target.Interior.ColorIndex = colorSeaGreen Target.Font.ColorIndex = colorSeaGreen Case Is = "DO" Target.Interior.ColorIndex = colorTurquoise Target.Font.ColorIndex = colorTurquoise Case Is = "DJ" Target.Interior.ColorIndex = colorRed Target.Font.ColorIndex = colorRed Case Is = "HH" Target.Interior.ColorIndex = colorYellow Target.Font.ColorIndex = colorYellow Case Is = "PCS" Target.Interior.ColorIndex = colorRed Target.Font.ColorIndex = colorRed Case Is = "LV" Target.Interior.ColorIndex = 1 Target.Font.ColorIndex = 1 Case Else 'do nothing End Select End Sub Hope this helps. "shekpatrick" wrote: hello again, more specifics on exactly what i am trying to do. i have 300 rows (record entries) containing people's names. These folks over the next 12 months are going to be going in a variety of locations. I have a large amount of columns set up as dates. I am going to limit the data entry into the date column/cell to the following: DB, DN, DS, DO, DJ, HH, PCS, LV As I am going to do a bunch of counting formulas for each day (so we can figure out the number of folks in each location), so I need to make the text entry into each cell. What I do not know how to do (since conditional formating only allows 3 conditions)...is this: In a cell, I enter "DB". This entry triggers the cell to fill with a certain color shade, as well as change the entered text to the same color. Here is the color scheme that I would like to use: DB (gray 40%), DN (bright green), DS (sea green), DO (turqoise), DJ (red), HH (yellow), PCS (red), LV (black). appreciate any help that anyone can provide!!! thanks in advance :)) |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
SHADING FOR MULTIPLE TEXT ENTRIES
Mr Latham,
My previous response was premature...here is the code that I have right now: Private Sub Worksheet_Change(ByVal Target As Range) Const colorGray40 = 48 Const colorRed = 3 Const colorBlack = 1 Const colorSeaGreen = 50 Const colorBrightGreen = 4 Const colorTurquoise = 8 Const colorYellow = 6 Const colorLavender = 39 Const colorLightOrange = 45 Const colorWhite = 2 Const colorViolet = 13 If Target.Cells.Count 1 Then Exit Sub End If Select Case UCase(Trim(Target)) Case Is = "DB" Target.Interior.ColorIndex = colorGray40 Target.Font.ColorIndex = colorGray40 Case Is = "DN" Target.Interior.ColorIndex = colorBrightGreen Target.Font.ColorIndex = colorBrightGreen Case Is = "DS" Target.Interior.ColorIndex = colorSeaGreen Target.Font.ColorIndex = colorSeaGreen Case Is = "DO" Target.Interior.ColorIndex = colorTurquoise Target.Font.ColorIndex = colorTurquoise Case Is = "DJ" Target.Interior.ColorIndex = colorRed Target.Font.ColorIndex = colorRed Case Is = "HH" Target.Interior.ColorIndex = colorYellow Target.Font.ColorIndex = colorYellow Case Is = "PCS" Target.Interior.ColorIndex = colorViolet Target.Font.ColorIndex = colorViolet Case Is = "PG" Target.Interior.ColorIndex = colorLavender Target.Font.ColorIndex = colorLavender Case Is = "LV" Target.Interior.ColorIndex = 1 Target.Font.ColorIndex = 1 Case Is = "TD" Target.Interior.ColorIndex = 45 Target.Font.ColorIndex = 45 Case Is = "" Target.Interior.ColorIndex = 2 Case Else 'do nothing End Select End Sub The column IDs are AF thru IU. My attempt to return the cell color to white when the text is deleted works, but you have to click/keystroke each cell individually...I can't select a cell range within a row and hit delete, and have the color return to white (though the text does delete). I also found out that when pasting in columns AF thru IU....while using a filter selection that limits rows, causes the paste to go into every cell. I can work around this by limiting my pasting to each row (record). thanks in advance! patrick "JLatham" wrote: There's actually more to the puzzle than you've told us: how/when do the colors in these cells return to normal?? Also, it would be a good thing to know the column IDs involved so that we could be more precise in our testing of the cells -- but the code below provides the basics. Read carefully - this code must go into the worksheet code area for the sheet where you are going to be typing in the "DB, "DJ, "PCS" etc entries. To get to the proper place, open your workbook, select that sheet and right-click on the sheet's name tab and choose [View Code] from the list that appears. Copy the code below and paste it into the empty code module that appeared when you clicked [View Code]. Close the VBA editor and give it a tryout. Private Sub Worksheet_Change(ByVal Target As Range) Const colorGray40 = 48 Const colorRed = 3 Const colorBlack = 1 Const colorSeaGreen = 50 Const colorBrightGreen = 4 Const colorTurquoise = 8 Const colorYellow = 6 If Target.Cells.Count 1 Then Exit Sub End If Select Case UCase(Trim(Target)) Case Is = "DB" Target.Interior.ColorIndex = colorGray40 Target.Font.ColorIndex = colorGray40 Case Is = "DN" Target.Interior.ColorIndex = colorBrightGreen Target.Font.ColorIndex = colorBrightGreen Case Is = "DS" Target.Interior.ColorIndex = colorSeaGreen Target.Font.ColorIndex = colorSeaGreen Case Is = "DO" Target.Interior.ColorIndex = colorTurquoise Target.Font.ColorIndex = colorTurquoise Case Is = "DJ" Target.Interior.ColorIndex = colorRed Target.Font.ColorIndex = colorRed Case Is = "HH" Target.Interior.ColorIndex = colorYellow Target.Font.ColorIndex = colorYellow Case Is = "PCS" Target.Interior.ColorIndex = colorRed Target.Font.ColorIndex = colorRed Case Is = "LV" Target.Interior.ColorIndex = 1 Target.Font.ColorIndex = 1 Case Else 'do nothing End Select End Sub Hope this helps. "shekpatrick" wrote: hello again, more specifics on exactly what i am trying to do. i have 300 rows (record entries) containing people's names. These folks over the next 12 months are going to be going in a variety of locations. I have a large amount of columns set up as dates. I am going to limit the data entry into the date column/cell to the following: DB, DN, DS, DO, DJ, HH, PCS, LV As I am going to do a bunch of counting formulas for each day (so we can figure out the number of folks in each location), so I need to make the text entry into each cell. What I do not know how to do (since conditional formating only allows 3 conditions)...is this: In a cell, I enter "DB". This entry triggers the cell to fill with a certain color shade, as well as change the entered text to the same color. Here is the color scheme that I would like to use: DB (gray 40%), DN (bright green), DS (sea green), DO (turqoise), DJ (red), HH (yellow), PCS (red), LV (black). appreciate any help that anyone can provide!!! thanks in advance :)) |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
SHADING FOR MULTIPLE TEXT ENTRIES
Did you get this straightened out, perhaps with Gord Dibben's help? Or not?
The system didn't notify me that you'd posted this and I just happened upon this today - don't want to leave you hanging if you need more assistance. "shekpatrick" wrote: Mr Latham, My previous response was premature...here is the code that I have right now: Private Sub Worksheet_Change(ByVal Target As Range) Const colorGray40 = 48 Const colorRed = 3 Const colorBlack = 1 Const colorSeaGreen = 50 Const colorBrightGreen = 4 Const colorTurquoise = 8 Const colorYellow = 6 Const colorLavender = 39 Const colorLightOrange = 45 Const colorWhite = 2 Const colorViolet = 13 If Target.Cells.Count 1 Then Exit Sub End If Select Case UCase(Trim(Target)) Case Is = "DB" Target.Interior.ColorIndex = colorGray40 Target.Font.ColorIndex = colorGray40 Case Is = "DN" Target.Interior.ColorIndex = colorBrightGreen Target.Font.ColorIndex = colorBrightGreen Case Is = "DS" Target.Interior.ColorIndex = colorSeaGreen Target.Font.ColorIndex = colorSeaGreen Case Is = "DO" Target.Interior.ColorIndex = colorTurquoise Target.Font.ColorIndex = colorTurquoise Case Is = "DJ" Target.Interior.ColorIndex = colorRed Target.Font.ColorIndex = colorRed Case Is = "HH" Target.Interior.ColorIndex = colorYellow Target.Font.ColorIndex = colorYellow Case Is = "PCS" Target.Interior.ColorIndex = colorViolet Target.Font.ColorIndex = colorViolet Case Is = "PG" Target.Interior.ColorIndex = colorLavender Target.Font.ColorIndex = colorLavender Case Is = "LV" Target.Interior.ColorIndex = 1 Target.Font.ColorIndex = 1 Case Is = "TD" Target.Interior.ColorIndex = 45 Target.Font.ColorIndex = 45 Case Is = "" Target.Interior.ColorIndex = 2 Case Else 'do nothing End Select End Sub The column IDs are AF thru IU. My attempt to return the cell color to white when the text is deleted works, but you have to click/keystroke each cell individually...I can't select a cell range within a row and hit delete, and have the color return to white (though the text does delete). I also found out that when pasting in columns AF thru IU....while using a filter selection that limits rows, causes the paste to go into every cell. I can work around this by limiting my pasting to each row (record). thanks in advance! patrick "JLatham" wrote: There's actually more to the puzzle than you've told us: how/when do the colors in these cells return to normal?? Also, it would be a good thing to know the column IDs involved so that we could be more precise in our testing of the cells -- but the code below provides the basics. Read carefully - this code must go into the worksheet code area for the sheet where you are going to be typing in the "DB, "DJ, "PCS" etc entries. To get to the proper place, open your workbook, select that sheet and right-click on the sheet's name tab and choose [View Code] from the list that appears. Copy the code below and paste it into the empty code module that appeared when you clicked [View Code]. Close the VBA editor and give it a tryout. Private Sub Worksheet_Change(ByVal Target As Range) Const colorGray40 = 48 Const colorRed = 3 Const colorBlack = 1 Const colorSeaGreen = 50 Const colorBrightGreen = 4 Const colorTurquoise = 8 Const colorYellow = 6 If Target.Cells.Count 1 Then Exit Sub End If Select Case UCase(Trim(Target)) Case Is = "DB" Target.Interior.ColorIndex = colorGray40 Target.Font.ColorIndex = colorGray40 Case Is = "DN" Target.Interior.ColorIndex = colorBrightGreen Target.Font.ColorIndex = colorBrightGreen Case Is = "DS" Target.Interior.ColorIndex = colorSeaGreen Target.Font.ColorIndex = colorSeaGreen Case Is = "DO" Target.Interior.ColorIndex = colorTurquoise Target.Font.ColorIndex = colorTurquoise Case Is = "DJ" Target.Interior.ColorIndex = colorRed Target.Font.ColorIndex = colorRed Case Is = "HH" Target.Interior.ColorIndex = colorYellow Target.Font.ColorIndex = colorYellow Case Is = "PCS" Target.Interior.ColorIndex = colorRed Target.Font.ColorIndex = colorRed Case Is = "LV" Target.Interior.ColorIndex = 1 Target.Font.ColorIndex = 1 Case Else 'do nothing End Select End Sub Hope this helps. "shekpatrick" wrote: hello again, more specifics on exactly what i am trying to do. i have 300 rows (record entries) containing people's names. These folks over the next 12 months are going to be going in a variety of locations. I have a large amount of columns set up as dates. I am going to limit the data entry into the date column/cell to the following: DB, DN, DS, DO, DJ, HH, PCS, LV As I am going to do a bunch of counting formulas for each day (so we can figure out the number of folks in each location), so I need to make the text entry into each cell. What I do not know how to do (since conditional formating only allows 3 conditions)...is this: In a cell, I enter "DB". This entry triggers the cell to fill with a certain color shade, as well as change the entered text to the same color. Here is the color scheme that I would like to use: DB (gray 40%), DN (bright green), DS (sea green), DO (turqoise), DJ (red), HH (yellow), PCS (red), LV (black). appreciate any help that anyone can provide!!! thanks in advance :)) |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
SHADING FOR MULTIPLE TEXT ENTRIES
I posted just one one reply Jerry.
No help for OP from me. OP seemed to want to follow your thread with the Select Case statements. Gord On Sun, 4 Jan 2009 18:19:01 -0800, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: Did you get this straightened out, perhaps with Gord Dibben's help? Or not? The system didn't notify me that you'd posted this and I just happened upon this today - don't want to leave you hanging if you need more assistance. "shekpatrick" wrote: Mr Latham, My previous response was premature...here is the code that I have right now: Private Sub Worksheet_Change(ByVal Target As Range) Const colorGray40 = 48 Const colorRed = 3 Const colorBlack = 1 Const colorSeaGreen = 50 Const colorBrightGreen = 4 Const colorTurquoise = 8 Const colorYellow = 6 Const colorLavender = 39 Const colorLightOrange = 45 Const colorWhite = 2 Const colorViolet = 13 If Target.Cells.Count 1 Then Exit Sub End If Select Case UCase(Trim(Target)) Case Is = "DB" Target.Interior.ColorIndex = colorGray40 Target.Font.ColorIndex = colorGray40 Case Is = "DN" Target.Interior.ColorIndex = colorBrightGreen Target.Font.ColorIndex = colorBrightGreen Case Is = "DS" Target.Interior.ColorIndex = colorSeaGreen Target.Font.ColorIndex = colorSeaGreen Case Is = "DO" Target.Interior.ColorIndex = colorTurquoise Target.Font.ColorIndex = colorTurquoise Case Is = "DJ" Target.Interior.ColorIndex = colorRed Target.Font.ColorIndex = colorRed Case Is = "HH" Target.Interior.ColorIndex = colorYellow Target.Font.ColorIndex = colorYellow Case Is = "PCS" Target.Interior.ColorIndex = colorViolet Target.Font.ColorIndex = colorViolet Case Is = "PG" Target.Interior.ColorIndex = colorLavender Target.Font.ColorIndex = colorLavender Case Is = "LV" Target.Interior.ColorIndex = 1 Target.Font.ColorIndex = 1 Case Is = "TD" Target.Interior.ColorIndex = 45 Target.Font.ColorIndex = 45 Case Is = "" Target.Interior.ColorIndex = 2 Case Else 'do nothing End Select End Sub The column IDs are AF thru IU. My attempt to return the cell color to white when the text is deleted works, but you have to click/keystroke each cell individually...I can't select a cell range within a row and hit delete, and have the color return to white (though the text does delete). I also found out that when pasting in columns AF thru IU....while using a filter selection that limits rows, causes the paste to go into every cell. I can work around this by limiting my pasting to each row (record). thanks in advance! patrick "JLatham" wrote: There's actually more to the puzzle than you've told us: how/when do the colors in these cells return to normal?? Also, it would be a good thing to know the column IDs involved so that we could be more precise in our testing of the cells -- but the code below provides the basics. Read carefully - this code must go into the worksheet code area for the sheet where you are going to be typing in the "DB, "DJ, "PCS" etc entries. To get to the proper place, open your workbook, select that sheet and right-click on the sheet's name tab and choose [View Code] from the list that appears. Copy the code below and paste it into the empty code module that appeared when you clicked [View Code]. Close the VBA editor and give it a tryout. Private Sub Worksheet_Change(ByVal Target As Range) Const colorGray40 = 48 Const colorRed = 3 Const colorBlack = 1 Const colorSeaGreen = 50 Const colorBrightGreen = 4 Const colorTurquoise = 8 Const colorYellow = 6 If Target.Cells.Count 1 Then Exit Sub End If Select Case UCase(Trim(Target)) Case Is = "DB" Target.Interior.ColorIndex = colorGray40 Target.Font.ColorIndex = colorGray40 Case Is = "DN" Target.Interior.ColorIndex = colorBrightGreen Target.Font.ColorIndex = colorBrightGreen Case Is = "DS" Target.Interior.ColorIndex = colorSeaGreen Target.Font.ColorIndex = colorSeaGreen Case Is = "DO" Target.Interior.ColorIndex = colorTurquoise Target.Font.ColorIndex = colorTurquoise Case Is = "DJ" Target.Interior.ColorIndex = colorRed Target.Font.ColorIndex = colorRed Case Is = "HH" Target.Interior.ColorIndex = colorYellow Target.Font.ColorIndex = colorYellow Case Is = "PCS" Target.Interior.ColorIndex = colorRed Target.Font.ColorIndex = colorRed Case Is = "LV" Target.Interior.ColorIndex = 1 Target.Font.ColorIndex = 1 Case Else 'do nothing End Select End Sub Hope this helps. "shekpatrick" wrote: hello again, more specifics on exactly what i am trying to do. i have 300 rows (record entries) containing people's names. These folks over the next 12 months are going to be going in a variety of locations. I have a large amount of columns set up as dates. I am going to limit the data entry into the date column/cell to the following: DB, DN, DS, DO, DJ, HH, PCS, LV As I am going to do a bunch of counting formulas for each day (so we can figure out the number of folks in each location), so I need to make the text entry into each cell. What I do not know how to do (since conditional formating only allows 3 conditions)...is this: In a cell, I enter "DB". This entry triggers the cell to fill with a certain color shade, as well as change the entered text to the same color. Here is the color scheme that I would like to use: DB (gray 40%), DN (bright green), DS (sea green), DO (turqoise), DJ (red), HH (yellow), PCS (red), LV (black). appreciate any help that anyone can provide!!! thanks in advance :)) |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
SHADING FOR MULTIPLE TEXT ENTRIES
Thanks,
I'll wait to see if the OP asks for more assistance - seems to still be in need of some, but I need to know if they're still following the thread and still need help. "Gord Dibben" wrote: I posted just one one reply Jerry. No help for OP from me. OP seemed to want to follow your thread with the Select Case statements. Gord On Sun, 4 Jan 2009 18:19:01 -0800, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: Did you get this straightened out, perhaps with Gord Dibben's help? Or not? The system didn't notify me that you'd posted this and I just happened upon this today - don't want to leave you hanging if you need more assistance. "shekpatrick" wrote: Mr Latham, My previous response was premature...here is the code that I have right now: Private Sub Worksheet_Change(ByVal Target As Range) Const colorGray40 = 48 Const colorRed = 3 Const colorBlack = 1 Const colorSeaGreen = 50 Const colorBrightGreen = 4 Const colorTurquoise = 8 Const colorYellow = 6 Const colorLavender = 39 Const colorLightOrange = 45 Const colorWhite = 2 Const colorViolet = 13 If Target.Cells.Count 1 Then Exit Sub End If Select Case UCase(Trim(Target)) Case Is = "DB" Target.Interior.ColorIndex = colorGray40 Target.Font.ColorIndex = colorGray40 Case Is = "DN" Target.Interior.ColorIndex = colorBrightGreen Target.Font.ColorIndex = colorBrightGreen Case Is = "DS" Target.Interior.ColorIndex = colorSeaGreen Target.Font.ColorIndex = colorSeaGreen Case Is = "DO" Target.Interior.ColorIndex = colorTurquoise Target.Font.ColorIndex = colorTurquoise Case Is = "DJ" Target.Interior.ColorIndex = colorRed Target.Font.ColorIndex = colorRed Case Is = "HH" Target.Interior.ColorIndex = colorYellow Target.Font.ColorIndex = colorYellow Case Is = "PCS" Target.Interior.ColorIndex = colorViolet Target.Font.ColorIndex = colorViolet Case Is = "PG" Target.Interior.ColorIndex = colorLavender Target.Font.ColorIndex = colorLavender Case Is = "LV" Target.Interior.ColorIndex = 1 Target.Font.ColorIndex = 1 Case Is = "TD" Target.Interior.ColorIndex = 45 Target.Font.ColorIndex = 45 Case Is = "" Target.Interior.ColorIndex = 2 Case Else 'do nothing End Select End Sub The column IDs are AF thru IU. My attempt to return the cell color to white when the text is deleted works, but you have to click/keystroke each cell individually...I can't select a cell range within a row and hit delete, and have the color return to white (though the text does delete). I also found out that when pasting in columns AF thru IU....while using a filter selection that limits rows, causes the paste to go into every cell. I can work around this by limiting my pasting to each row (record). thanks in advance! patrick "JLatham" wrote: There's actually more to the puzzle than you've told us: how/when do the colors in these cells return to normal?? Also, it would be a good thing to know the column IDs involved so that we could be more precise in our testing of the cells -- but the code below provides the basics. Read carefully - this code must go into the worksheet code area for the sheet where you are going to be typing in the "DB, "DJ, "PCS" etc entries. To get to the proper place, open your workbook, select that sheet and right-click on the sheet's name tab and choose [View Code] from the list that appears. Copy the code below and paste it into the empty code module that appeared when you clicked [View Code]. Close the VBA editor and give it a tryout. Private Sub Worksheet_Change(ByVal Target As Range) Const colorGray40 = 48 Const colorRed = 3 Const colorBlack = 1 Const colorSeaGreen = 50 Const colorBrightGreen = 4 Const colorTurquoise = 8 Const colorYellow = 6 If Target.Cells.Count 1 Then Exit Sub End If Select Case UCase(Trim(Target)) Case Is = "DB" Target.Interior.ColorIndex = colorGray40 Target.Font.ColorIndex = colorGray40 Case Is = "DN" Target.Interior.ColorIndex = colorBrightGreen Target.Font.ColorIndex = colorBrightGreen Case Is = "DS" Target.Interior.ColorIndex = colorSeaGreen Target.Font.ColorIndex = colorSeaGreen Case Is = "DO" Target.Interior.ColorIndex = colorTurquoise Target.Font.ColorIndex = colorTurquoise Case Is = "DJ" Target.Interior.ColorIndex = colorRed Target.Font.ColorIndex = colorRed Case Is = "HH" Target.Interior.ColorIndex = colorYellow Target.Font.ColorIndex = colorYellow Case Is = "PCS" Target.Interior.ColorIndex = colorRed Target.Font.ColorIndex = colorRed Case Is = "LV" Target.Interior.ColorIndex = 1 Target.Font.ColorIndex = 1 Case Else 'do nothing End Select End Sub Hope this helps. "shekpatrick" wrote: hello again, more specifics on exactly what i am trying to do. i have 300 rows (record entries) containing people's names. These folks over the next 12 months are going to be going in a variety of locations. I have a large amount of columns set up as dates. I am going to limit the data entry into the date column/cell to the following: DB, DN, DS, DO, DJ, HH, PCS, LV As I am going to do a bunch of counting formulas for each day (so we can figure out the number of folks in each location), so I need to make the text entry into each cell. What I do not know how to do (since conditional formating only allows 3 conditions)...is this: In a cell, I enter "DB". This entry triggers the cell to fill with a certain color shade, as well as change the entered text to the same color. Here is the color scheme that I would like to use: DB (gray 40%), DN (bright green), DS (sea green), DO (turqoise), DJ (red), HH (yellow), PCS (red), LV (black). appreciate any help that anyone can provide!!! thanks in advance :)) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| color shading for text entries | New Users to Excel | |||
| Combine Multiple Entries with differing amounts of entries | Excel Worksheet Functions | |||
| Convert Multiple Text entries... | Excel Worksheet Functions | |||
| Count single Text in cells with multiple text entries | Excel Discussion (Misc queries) | |||
| UserForms - Viewing multiple entries in a text box. | Excel Discussion (Misc queries) | |||
Linear Mode
