| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional formatting
I am using Excel 2000 and I'm wondering if there is a way to have more than
three arguments in the conditional formatting. I have a "calendar" that has employee's names and the days they have taken time off .and I would like to highlight a few of the names for the whole yaer without having to search for each name. Any suggestions?? Tahnk you |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional formatting
Chris, The pasted section below is Bob Phillips' answer to the same question a while ago (I just do the copy/pasting) ++++++++++++++++++ I want to use more than 3 conditions, I know that there is a way to use VBA to do this but I have no idea of how to use vba. What I have is a list contained in a cell from which the user picks a word and I want each word to have an associated colour appear in another cell. I cant seem to understand how the VB codes are supposed to work so i have not been able to adapt them from other users suggestions I cannot use the tool in www.xldynamic.com/source/xld.CFPlus as Im not allowed to download it, also multiple users will need to use the s/sheet What I need to do is when a user selects "On track" in the list the corresponding cell in the work sheet needs to turn green "completed" needs to be blue "overdue" needs to be red "late" needs to be red "problem" needs to be red "Agreed" needs to be purple "confirmed" needs to be purple would greatly appreciate any help DD #2 Today, 10:30 AM Bob Phillips Posts: n/a Conditional formatting -------------------------------------------------------------------------------- Option Explicit Public Enum xlColorIndex xlCIBlack = 1 xlCIWhite = 2 xlCIRed = 3 xlCIBrightGreen = 4 xlCIBlue = 5 xlCIYellow = 6 xlCIPink = 7 xlCITurquoise = 8 xlCIDarkRed = 9 xlCIGreen = 10 xlCIDarkBlue = 11 xlCIDarkYellow = 12 xlCIViolet = 13 xlCITeal = 14 xlCIGray25 = 15 xlCIGray50 = 16 xlCIPeriwinkle = 17 xlCIPlum = 18 xlCIIvory = 19 xlCILightTurquoise = 20 xlCIDarkPurple = 21 xlCICoral = 22 xlCIOceanBlue = 23 xlCIIceBlue = 24 'xlCIDarkBlue = 25 'xlCIPink = 26 'xlCIYellow = 27 'xlCITurquoise = 28 'xlCIViolet = 29 'xlCIDarkRed = 30 'xlCITeal = 31 'xlCIBlue = 32 xlCISkyBlue = 33 xlCILightGreen = 35 xlCILightYellow = 36 xlCIPaleBlue = 37 xlCIRose = 38 xlCILavender = 39 xlCITan = 40 xlCILightBlue = 41 xlCIAqua = 42 xlCILime = 43 xlCIGold = 44 xlCILightOrange = 45 xlCIOrange = 46 xlCIBlueGray = 47 xlCIGray40 = 48 xlCIDarkTeal = 49 xlCISeaGreen = 50 xlCIDarkGreen = 51 xlCIBrown = 53 xlCIIndigo = 55 xlCIGray80 = 56 End Enum '----------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- Const WS_RANGE As String = "H1:H10" '<=== Change to suit On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Select Case LCase(.Value) Case "overdue", "late", "problem": .Interior.ColorIndex = xlCIRed Case "agreed", "confirmed: " .Interior.ColorIndex = xlCILavender Case "completed": .Interior.ColorIndex = xlCIBlue Case "On track": .Interior.ColorIndex = xlCIGreen End Select End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "DD" wrote in message ... I want to use more than 3 conditions, I know that there is a way to use VBA to do this but I have no idea of how to use vba. What I have is a list contained in a cell from which the user picks a word and I want each word to have an associated colour appear in another cell. I cant seem to understand how the VB codes are supposed to work so i have not been able to adapt them from other users suggestions I cannot use the tool in www.xldynamic.com/source/xld.CFPlus as Im not allowed to download it, also multiple users will need to use the s/sheet What I need to do is when a user selects "On track" in the list the corresponding cell in the work sheet needs to turn green "completed" needs to be blue "overdue" needs to be red "late" needs to be red "problem" needs to be red "Agreed" needs to be purple "confirmed" needs to be purple would greatly appreciate any help -- Jon Quixley ------------------------------------------------------------------------ Jon Quixley's Profile: http://www.excelforum.com/member.php...o&userid=25803 View this thread: http://www.excelforum.com/showthread...hreadid=565289 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
conditional formatting
Frist I'd like to thank you for your time and the code but I have a problem
with it. I know very little VB. I keep getting a compile error: Ambiguous name detected: xlColorIndex "Jon Quixley" wrote: Chris, The pasted section below is Bob Phillips' answer to the same question a while ago (I just do the copy/pasting) ++++++++++++++++++ I want to use more than 3 conditions, I know that there is a way to use VBA to do this but I have no idea of how to use vba. What I have is a list contained in a cell from which the user picks a word and I want each word to have an associated colour appear in another cell. I cant seem to understand how the VB codes are supposed to work so i have not been able to adapt them from other users suggestions I cannot use the tool in www.xldynamic.com/source/xld.CFPlus as Im not allowed to download it, also multiple users will need to use the s/sheet What I need to do is when a user selects "On track" in the list the corresponding cell in the work sheet needs to turn green "completed" needs to be blue "overdue" needs to be red "late" needs to be red "problem" needs to be red "Agreed" needs to be purple "confirmed" needs to be purple would greatly appreciate any help DD #2 Today, 10:30 AM Bob Phillips Posts: n/a Conditional formatting -------------------------------------------------------------------------------- Option Explicit Public Enum xlColorIndex xlCIBlack = 1 xlCIWhite = 2 xlCIRed = 3 xlCIBrightGreen = 4 xlCIBlue = 5 xlCIYellow = 6 xlCIPink = 7 xlCITurquoise = 8 xlCIDarkRed = 9 xlCIGreen = 10 xlCIDarkBlue = 11 xlCIDarkYellow = 12 xlCIViolet = 13 xlCITeal = 14 xlCIGray25 = 15 xlCIGray50 = 16 xlCIPeriwinkle = 17 xlCIPlum = 18 xlCIIvory = 19 xlCILightTurquoise = 20 xlCIDarkPurple = 21 xlCICoral = 22 xlCIOceanBlue = 23 xlCIIceBlue = 24 'xlCIDarkBlue = 25 'xlCIPink = 26 'xlCIYellow = 27 'xlCITurquoise = 28 'xlCIViolet = 29 'xlCIDarkRed = 30 'xlCITeal = 31 'xlCIBlue = 32 xlCISkyBlue = 33 xlCILightGreen = 35 xlCILightYellow = 36 xlCIPaleBlue = 37 xlCIRose = 38 xlCILavender = 39 xlCITan = 40 xlCILightBlue = 41 xlCIAqua = 42 xlCILime = 43 xlCIGold = 44 xlCILightOrange = 45 xlCIOrange = 46 xlCIBlueGray = 47 xlCIGray40 = 48 xlCIDarkTeal = 49 xlCISeaGreen = 50 xlCIDarkGreen = 51 xlCIBrown = 53 xlCIIndigo = 55 xlCIGray80 = 56 End Enum '----------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- Const WS_RANGE As String = "H1:H10" '<=== Change to suit On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Select Case LCase(.Value) Case "overdue", "late", "problem": .Interior.ColorIndex = xlCIRed Case "agreed", "confirmed: " .Interior.ColorIndex = xlCILavender Case "completed": .Interior.ColorIndex = xlCIBlue Case "On track": .Interior.ColorIndex = xlCIGreen End Select End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "DD" wrote in message ... I want to use more than 3 conditions, I know that there is a way to use VBA to do this but I have no idea of how to use vba. What I have is a list contained in a cell from which the user picks a word and I want each word to have an associated colour appear in another cell. I cant seem to understand how the VB codes are supposed to work so i have not been able to adapt them from other users suggestions I cannot use the tool in www.xldynamic.com/source/xld.CFPlus as Im not allowed to download it, also multiple users will need to use the s/sheet What I need to do is when a user selects "On track" in the list the corresponding cell in the work sheet needs to turn green "completed" needs to be blue "overdue" needs to be red "late" needs to be red "problem" needs to be red "Agreed" needs to be purple "confirmed" needs to be purple would greatly appreciate any help -- Jon Quixley ------------------------------------------------------------------------ Jon Quixley's Profile: http://www.excelforum.com/member.php...o&userid=25803 View this thread: http://www.excelforum.com/showthread...hreadid=565289 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Compile Error: Ambiguous Name
Chris, were you able to figure this out. I just put in some code in VB a few
minutes ago and I am getting the same message. I notice that some of the code I copied to run a different macro in the same sheet has the same line: Private Sub Worksheet_Change (ByVal Target As Range). as the new code I put in this morning. Thanks for any help, especially if you could get back to me right away. "Chris" wrote: Frist I'd like to thank you for your time and the code but I have a problem with it. I know very little VB. I keep getting a compile error: Ambiguous name detected: xlColorIndex "Jon Quixley" wrote: Chris, The pasted section below is Bob Phillips' answer to the same question a while ago (I just do the copy/pasting) ++++++++++++++++++ I want to use more than 3 conditions, I know that there is a way to use VBA to do this but I have no idea of how to use vba. What I have is a list contained in a cell from which the user picks a word and I want each word to have an associated colour appear in another cell. I cant seem to understand how the VB codes are supposed to work so i have not been able to adapt them from other users suggestions I cannot use the tool in www.xldynamic.com/source/xld.CFPlus as Im not allowed to download it, also multiple users will need to use the s/sheet What I need to do is when a user selects "On track" in the list the corresponding cell in the work sheet needs to turn green "completed" needs to be blue "overdue" needs to be red "late" needs to be red "problem" needs to be red "Agreed" needs to be purple "confirmed" needs to be purple would greatly appreciate any help DD #2 Today, 10:30 AM Bob Phillips Posts: n/a Conditional formatting -------------------------------------------------------------------------------- Option Explicit Public Enum xlColorIndex xlCIBlack = 1 xlCIWhite = 2 xlCIRed = 3 xlCIBrightGreen = 4 xlCIBlue = 5 xlCIYellow = 6 xlCIPink = 7 xlCITurquoise = 8 xlCIDarkRed = 9 xlCIGreen = 10 xlCIDarkBlue = 11 xlCIDarkYellow = 12 xlCIViolet = 13 xlCITeal = 14 xlCIGray25 = 15 xlCIGray50 = 16 xlCIPeriwinkle = 17 xlCIPlum = 18 xlCIIvory = 19 xlCILightTurquoise = 20 xlCIDarkPurple = 21 xlCICoral = 22 xlCIOceanBlue = 23 xlCIIceBlue = 24 'xlCIDarkBlue = 25 'xlCIPink = 26 'xlCIYellow = 27 'xlCITurquoise = 28 'xlCIViolet = 29 'xlCIDarkRed = 30 'xlCITeal = 31 'xlCIBlue = 32 xlCISkyBlue = 33 xlCILightGreen = 35 xlCILightYellow = 36 xlCIPaleBlue = 37 xlCIRose = 38 xlCILavender = 39 xlCITan = 40 xlCILightBlue = 41 xlCIAqua = 42 xlCILime = 43 xlCIGold = 44 xlCILightOrange = 45 xlCIOrange = 46 xlCIBlueGray = 47 xlCIGray40 = 48 xlCIDarkTeal = 49 xlCISeaGreen = 50 xlCIDarkGreen = 51 xlCIBrown = 53 xlCIIndigo = 55 xlCIGray80 = 56 End Enum '----------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- Const WS_RANGE As String = "H1:H10" '<=== Change to suit On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Select Case LCase(.Value) Case "overdue", "late", "problem": .Interior.ColorIndex = xlCIRed Case "agreed", "confirmed: " .Interior.ColorIndex = xlCILavender Case "completed": .Interior.ColorIndex = xlCIBlue Case "On track": .Interior.ColorIndex = xlCIGreen End Select End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "DD" wrote in message ... I want to use more than 3 conditions, I know that there is a way to use VBA to do this but I have no idea of how to use vba. What I have is a list contained in a cell from which the user picks a word and I want each word to have an associated colour appear in another cell. I cant seem to understand how the VB codes are supposed to work so i have not been able to adapt them from other users suggestions I cannot use the tool in www.xldynamic.com/source/xld.CFPlus as Im not allowed to download it, also multiple users will need to use the s/sheet What I need to do is when a user selects "On track" in the list the corresponding cell in the work sheet needs to turn green "completed" needs to be blue "overdue" needs to be red "late" needs to be red "problem" needs to be red "Agreed" needs to be purple "confirmed" needs to be purple would greatly appreciate any help -- Jon Quixley ------------------------------------------------------------------------ Jon Quixley's Profile: http://www.excelforum.com/member.php...o&userid=25803 View this thread: http://www.excelforum.com/showthread...hreadid=565289 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sorting a range with conditional formatting | Excel Worksheet Functions | |||
| conditional formatting glitches | Excel Discussion (Misc queries) | |||
| Keeping conditional formatting when sorting | Excel Discussion (Misc queries) | |||
| conditional formatting | Excel Discussion (Misc queries) | |||
| cannot use ISEVEN or ISODD functions in Conditional Formatting | Excel Worksheet Functions | |||
Linear Mode
