conditional formatting

[Chris]

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

[Jon Quixley]

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

Re: 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" <DD@discussions.microsoft.com> wrote in message
news:1618E30C-0151-49CD-8666-84ED649254A9@microsoft.com...
> 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

[Chris]

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
>
> Re: 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" <DD@discussions.microsoft.com> wrote in message
> news:1618E30C-0151-49CD-8666-84ED649254A9@microsoft.com...
> > 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
>
>