| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting More Than 3 Items
I have a worksheet with data in A27:O250, and I need to color code each
individual row based on the value in column C of that row. There will be at least 6-8 different values in column C from which I need to base the row color from, so conditional formatting won't work. Any help is greatly appreciated! Thanks |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Formatting More Than 3 Items
Can you give us a hint what those 6-8 different values might be (or look
like)? And are you saying you will not know in advance how many different values there might be? If so, is there a maximum number of different values possible? Also, I'm guessing you will want a different color for each different value, right? -- Rick (MVP - Excel) "stevedemo77" wrote in message ... I have a worksheet with data in A27:O250, and I need to color code each individual row based on the value in column C of that row. There will be at least 6-8 different values in column C from which I need to base the row color from, so conditional formatting won't work. Any help is greatly appreciated! Thanks |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Formatting More Than 3 Items
Yes, the values a ACT, BLD, BUD, CV, CVA, IRL, REV, and each one would
have a different color, and if it were any other value there would be no color needed. This should be the entire list of possible values. Thanks! "Rick Rothstein" wrote: Can you give us a hint what those 6-8 different values might be (or look like)? And are you saying you will not know in advance how many different values there might be? If so, is there a maximum number of different values possible? Also, I'm guessing you will want a different color for each different value, right? -- Rick (MVP - Excel) "stevedemo77" wrote in message ... I have a worksheet with data in A27:O250, and I need to color code each individual row based on the value in column C of that row. There will be at least 6-8 different values in column C from which I need to base the row color from, so conditional formatting won't work. Any help is greatly appreciated! Thanks |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Formatting More Than 3 Items
Right-click on the tab of the Sheet that you need the code for. paste this
into the window that opens: Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Range Set vRngInput = Intersect(Target, Range("A:Z")) '<---------change to suit If vRngInput Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False For Each rng In vRngInput 'Determine the color Select Case UCase(rng.Value) Case Is = "ACT": Num = 10 'green Case Is = "BLD": Num = 2 'black Case Is = "BUD": Num = 5 'blue Case Is = "CV": Num = 7 'magenta Case Is = "CVA": Num = 46 'orange Case Is = "IRL": Num = 3 'red Case Is = "REV": Num = 4 ' End Select 'Apply the color rng.Interior.ColorIndex = Num Next rng endit: Application.EnableEvents = True End Sub Here is a list of excel colors: http://www.mvps.org/dmcritchie/excel/colors.htm HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "stevedemo77" wrote: Yes, the values a ACT, BLD, BUD, CV, CVA, IRL, REV, and each one would have a different color, and if it were any other value there would be no color needed. This should be the entire list of possible values. Thanks! "Rick Rothstein" wrote: Can you give us a hint what those 6-8 different values might be (or look like)? And are you saying you will not know in advance how many different values there might be? If so, is there a maximum number of different values possible? Also, I'm guessing you will want a different color for each different value, right? -- Rick (MVP - Excel) "stevedemo77" wrote in message ... I have a worksheet with data in A27:O250, and I need to color code each individual row based on the value in column C of that row. There will be at least 6-8 different values in column C from which I need to base the row color from, so conditional formatting won't work. Any help is greatly appreciated! Thanks |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Formatting More Than 3 Items
Is there something else I need to do besides change the range? This doesn't
appear to do anything. "ryguy7272" wrote: Right-click on the tab of the Sheet that you need the code for. paste this into the window that opens: Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Range Set vRngInput = Intersect(Target, Range("A:Z")) '<---------change to suit If vRngInput Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False For Each rng In vRngInput 'Determine the color Select Case UCase(rng.Value) Case Is = "ACT": Num = 10 'green Case Is = "BLD": Num = 2 'black Case Is = "BUD": Num = 5 'blue Case Is = "CV": Num = 7 'magenta Case Is = "CVA": Num = 46 'orange Case Is = "IRL": Num = 3 'red Case Is = "REV": Num = 4 ' End Select 'Apply the color rng.Interior.ColorIndex = Num Next rng endit: Application.EnableEvents = True End Sub Here is a list of excel colors: http://www.mvps.org/dmcritchie/excel/colors.htm HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "stevedemo77" wrote: Yes, the values a ACT, BLD, BUD, CV, CVA, IRL, REV, and each one would have a different color, and if it were any other value there would be no color needed. This should be the entire list of possible values. Thanks! "Rick Rothstein" wrote: Can you give us a hint what those 6-8 different values might be (or look like)? And are you saying you will not know in advance how many different values there might be? If so, is there a maximum number of different values possible? Also, I'm guessing you will want a different color for each different value, right? -- Rick (MVP - Excel) "stevedemo77" wrote in message ... I have a worksheet with data in A27:O250, and I need to color code each individual row based on the value in column C of that row. There will be at least 6-8 different values in column C from which I need to base the row color from, so conditional formatting won't work. Any help is greatly appreciated! Thanks |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Formatting More Than 3 Items
The code Ryan posted works automatically (similar to how Conditional
Formatting would have)... type one of your codes into a cell within the range you changed the example Range("A:Z") to and it should become colored in (with the exception maybe of BLD since Black, on my system is showing as white). -- Rick (MVP - Excel) "stevedemo77" wrote in message ... Is there something else I need to do besides change the range? This doesn't appear to do anything. "ryguy7272" wrote: Right-click on the tab of the Sheet that you need the code for. paste this into the window that opens: Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Range Set vRngInput = Intersect(Target, Range("A:Z")) '<---------change to suit If vRngInput Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False For Each rng In vRngInput 'Determine the color Select Case UCase(rng.Value) Case Is = "ACT": Num = 10 'green Case Is = "BLD": Num = 2 'black Case Is = "BUD": Num = 5 'blue Case Is = "CV": Num = 7 'magenta Case Is = "CVA": Num = 46 'orange Case Is = "IRL": Num = 3 'red Case Is = "REV": Num = 4 ' End Select 'Apply the color rng.Interior.ColorIndex = Num Next rng endit: Application.EnableEvents = True End Sub Here is a list of excel colors: http://www.mvps.org/dmcritchie/excel/colors.htm HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "stevedemo77" wrote: Yes, the values a ACT, BLD, BUD, CV, CVA, IRL, REV, and each one would have a different color, and if it were any other value there would be no color needed. This should be the entire list of possible values. Thanks! "Rick Rothstein" wrote: Can you give us a hint what those 6-8 different values might be (or look like)? And are you saying you will not know in advance how many different values there might be? If so, is there a maximum number of different values possible? Also, I'm guessing you will want a different color for each different value, right? -- Rick (MVP - Excel) "stevedemo77" wrote in message ... I have a worksheet with data in A27:O250, and I need to color code each individual row based on the value in column C of that row. There will be at least 6-8 different values in column C from which I need to base the row color from, so conditional formatting won't work. Any help is greatly appreciated! Thanks |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Formatting More Than 3 Items
Strange...still not working. I must be missing something.
"Rick Rothstein" wrote: The code Ryan posted works automatically (similar to how Conditional Formatting would have)... type one of your codes into a cell within the range you changed the example Range("A:Z") to and it should become colored in (with the exception maybe of BLD since Black, on my system is showing as white). -- Rick (MVP - Excel) "stevedemo77" wrote in message ... Is there something else I need to do besides change the range? This doesn't appear to do anything. "ryguy7272" wrote: Right-click on the tab of the Sheet that you need the code for. paste this into the window that opens: Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Range Set vRngInput = Intersect(Target, Range("A:Z")) '<---------change to suit If vRngInput Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False For Each rng In vRngInput 'Determine the color Select Case UCase(rng.Value) Case Is = "ACT": Num = 10 'green Case Is = "BLD": Num = 2 'black Case Is = "BUD": Num = 5 'blue Case Is = "CV": Num = 7 'magenta Case Is = "CVA": Num = 46 'orange Case Is = "IRL": Num = 3 'red Case Is = "REV": Num = 4 ' End Select 'Apply the color rng.Interior.ColorIndex = Num Next rng endit: Application.EnableEvents = True End Sub Here is a list of excel colors: http://www.mvps.org/dmcritchie/excel/colors.htm HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "stevedemo77" wrote: Yes, the values a ACT, BLD, BUD, CV, CVA, IRL, REV, and each one would have a different color, and if it were any other value there would be no color needed. This should be the entire list of possible values. Thanks! "Rick Rothstein" wrote: Can you give us a hint what those 6-8 different values might be (or look like)? And are you saying you will not know in advance how many different values there might be? If so, is there a maximum number of different values possible? Also, I'm guessing you will want a different color for each different value, right? -- Rick (MVP - Excel) "stevedemo77" wrote in message ... I have a worksheet with data in A27:O250, and I need to color code each individual row based on the value in column C of that row. There will be at least 6-8 different values in column C from which I need to base the row color from, so conditional formatting won't work. Any help is greatly appreciated! Thanks |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Conditional Formatting More Than 3 Items
Paste that code behind the sheet (right-click the sheet and paste into the
window that opens). Then go back to the sheet and type ACT into Cell A1. What happens? HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "stevedemo77" wrote: Strange...still not working. I must be missing something. "Rick Rothstein" wrote: The code Ryan posted works automatically (similar to how Conditional Formatting would have)... type one of your codes into a cell within the range you changed the example Range("A:Z") to and it should become colored in (with the exception maybe of BLD since Black, on my system is showing as white). -- Rick (MVP - Excel) "stevedemo77" wrote in message ... Is there something else I need to do besides change the range? This doesn't appear to do anything. "ryguy7272" wrote: Right-click on the tab of the Sheet that you need the code for. paste this into the window that opens: Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Range Set vRngInput = Intersect(Target, Range("A:Z")) '<---------change to suit If vRngInput Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False For Each rng In vRngInput 'Determine the color Select Case UCase(rng.Value) Case Is = "ACT": Num = 10 'green Case Is = "BLD": Num = 2 'black Case Is = "BUD": Num = 5 'blue Case Is = "CV": Num = 7 'magenta Case Is = "CVA": Num = 46 'orange Case Is = "IRL": Num = 3 'red Case Is = "REV": Num = 4 ' End Select 'Apply the color rng.Interior.ColorIndex = Num Next rng endit: Application.EnableEvents = True End Sub Here is a list of excel colors: http://www.mvps.org/dmcritchie/excel/colors.htm HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "stevedemo77" wrote: Yes, the values a ACT, BLD, BUD, CV, CVA, IRL, REV, and each one would have a different color, and if it were any other value there would be no color needed. This should be the entire list of possible values. Thanks! "Rick Rothstein" wrote: Can you give us a hint what those 6-8 different values might be (or look like)? And are you saying you will not know in advance how many different values there might be? If so, is there a maximum number of different values possible? Also, I'm guessing you will want a different color for each different value, right? -- Rick (MVP - Excel) "stevedemo77" wrote in message ... I have a worksheet with data in A27:O250, and I need to color code each individual row based on the value in column C of that row. There will be at least 6-8 different values in column C from which I need to base the row color from, so conditional formatting won't work. Any help is greatly appreciated! Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| conditional formatting formula to highlight items on column B inrelation to column A | Excel Discussion (Misc queries) | |||
| How can I compare more than 7 items in a conditional statement? | Setting up and Configuration of Excel | |||
| I would like to use conditional formatting for 4 or more items. | Excel Discussion (Misc queries) | |||
| Conditional formating - Highlighting items in a separate column | Excel Discussion (Misc queries) | |||
| Sum Conditional Items by date across workbooks | Excel Worksheet Functions | |||
Linear Mode
