more than 3 conditions in conditional formatting - possible?

Is it possible to have more than 3 conditions using conditional formatting?

I am dealing with text and not numbers which makes it a little harder to 
manipulate.  I want up to 10 conditions.

Can anyone advise?  Cheers, rob
0
Curtis (31)
8/10/2005 10:45:14 AM
excel.misc 78881 articles. 5 followers. Follow

11 Replies
329 Views

Similar Articles

[PageSpeed] 38

There is a tool on the net called CFPlus which lets you use loads. 

Try out www.xlDynamic.com

"rob curtis" wrote:

> Is it possible to have more than 3 conditions using conditional formatting?
> 
> I am dealing with text and not numbers which makes it a little harder to 
> manipulate.  I want up to 10 conditions.
> 
> Can anyone advise?  Cheers, rob
0
Kay (174)
8/10/2005 11:29:03 AM
Rob,

Yes with event code. Basically of the form

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo ws_exit:
    Application.EnableEvents = False
    If Not Intersect(Target, Me.Range("H1:H10")) Is Nothing Then
        With Target
            Select Case .Value
                Case "value 1": 'do something
                Case "value 2": 'do something else
                'etc
            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

RP
(remove nothere from the email address if mailing direct)


"rob curtis" <rob curtis@discussions.microsoft.com> wrote in message
news:580BCCDB-6DB2-43EB-807C-DBE9DB57AB4E@microsoft.com...
> Is it possible to have more than 3 conditions using conditional
formatting?
>
> I am dealing with text and not numbers which makes it a little harder to
> manipulate.  I want up to 10 conditions.
>
> Can anyone advise?  Cheers, rob


0
bob.phillips1 (6510)
8/10/2005 11:40:49 AM
Hi Bob,

Thanks for your reply. I am a friend of Rob's who is helping him out with 
this.

Just to understand your code, I presume H1:H10 is the range of values that 
you are considering, and the "value 1", "value 2" etc are the values that you 
are looking for in that range. Hence the "do something" is what you want 
doing when a value in your range equals one of your spefied values.

What we are trying to achieve is to say if a value in a range is "red", then 
that cell turns red. If the value is "orange", the cell turns orange.

As such the "do something" code would look something like:
        .ColorIndex = 6
        .Pattern = xlSolid
but I don't know how to refer to the cell that contains the value to change 
the formatting as appropriate. Can you (or anyone else) help?

"Bob Phillips" wrote:

> Rob,
> 
> Yes with event code. Basically of the form
> 
> Private Sub Worksheet_Change(ByVal Target As Range)
> 
>     On Error GoTo ws_exit:
>     Application.EnableEvents = False
>     If Not Intersect(Target, Me.Range("H1:H10")) Is Nothing Then
>         With Target
>             Select Case .Value
>                 Case "value 1": 'do something
>                 Case "value 2": 'do something else
>                 'etc
>             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
> 
> RP
> (remove nothere from the email address if mailing direct)
> 
> 
> "rob curtis" <rob curtis@discussions.microsoft.com> wrote in message
> news:580BCCDB-6DB2-43EB-807C-DBE9DB57AB4E@microsoft.com...
> > Is it possible to have more than 3 conditions using conditional
> formatting?
> >
> > I am dealing with text and not numbers which makes it a little harder to
> > manipulate.  I want up to 10 conditions.
> >
> > Can anyone advise?  Cheers, rob
> 
> 
> 
0
8/10/2005 2:33:29 PM
Thanks for this Kay,

Will each use who wants to view the spreadsheet need to download that same 
add-in?


"Kay" wrote:

> There is a tool on the net called CFPlus which lets you use loads. 
> 
> Try out www.xlDynamic.com
> 
> "rob curtis" wrote:
> 
> > Is it possible to have more than 3 conditions using conditional formatting?
> > 
> > I am dealing with text and not numbers which makes it a little harder to 
> > manipulate.  I want up to 10 conditions.
> > 
> > Can anyone advise?  Cheers, rob
0
8/10/2005 2:39:03 PM
Yes

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"JonnyCrabb" <JonnyCrabb@discussions.microsoft.com> wrote in message
news:2850FB9E-CEE9-4E78-A045-6F77CA0A6A87@microsoft.com...
> Thanks for this Kay,
>
> Will each use who wants to view the spreadsheet need to download that same
> add-in?
>
>
> "Kay" wrote:
>
> > There is a tool on the net called CFPlus which lets you use loads.
> >
> > Try out www.xlDynamic.com
> >
> > "rob curtis" wrote:
> >
> > > Is it possible to have more than 3 conditions using conditional
formatting?
> > >
> > > I am dealing with text and not numbers which makes it a little harder
to
> > > manipulate.  I want up to 10 conditions.
> > >
> > > Can anyone advise?  Cheers, rob


0
bob.phillips1 (6510)
8/10/2005 6:10:30 PM
"JonnyCrabb" <JonnyCrabb@discussions.microsoft.com> wrote in message
news:F6E796A6-B7D8-4A61-97C3-B7D833DFC31F@microsoft.com...
> Hi Bob,
>
> Thanks for your reply. I am a friend of Rob's who is helping him out with
> this.

No problems, anyone can join in.

> Just to understand your code, I presume H1:H10 is the range of values that
> you are considering, and the "value 1", "value 2" etc are the values that
you
> are looking for in that range. Hence the "do something" is what you want
> doing when a value in your range equals one of your spefied values.

Correct on all points.

> What we are trying to achieve is to say if a value in a range is "red",
then
> that cell turns red. If the value is "orange", the cell turns orange.


I thought I did this self same thing a few days ago but I can't find it.
> As such the "do something" code would look something like:
>         .ColorIndex = 6
>         .Pattern = xlSolid
> but I don't know how to refer to the cell that contains the value to
change
> the formatting as appropriate. Can you (or anyone else) help?

Option Explicit

Private Const xlCIBlack As Long = 1
Private Const xlCIWhite As Long = 2
Private Const xlCIRed As Long = 3
Private Const xlCIBrightGreen As Long = 4
Private Const xlCIBlue As Long = 5
Private Const xlCIYellow As Long = 6
Private Const xlCIPink As Long = 7
Private Const xlCITurquoise As Long = 8
Private Const xlCIDarkRed As Long = 9
Private Const xlCIGreen As Long = 10
Private Const xlCIDarkBlue As Long = 11
Private Const xlCIDarkYellow As Long = 12
Private Const xlCIViolet As Long = 13
Private Const xlCITeal As Long = 14
Private Const xlCIGray25 As Long = 15
Private Const xlCIGray40 As Long = 16
Private Const xlCIPaleBlue As Long = 17
Private Const xlCIPlum As Long = 18
Private Const xlCILightTurquoise As Long = 20
Private Const xlCILightBlue As Long = 23
Private Const xlCIBrown As Long = 30
Private Const xlCISkyBlue As Long = 33
Private Const xlCILightGreen As Long = 35
Private Const xlCILightYellow As Long = 36
Private Const xlCILavender As Long = 39
Private Const xlCIAqua As Long = 42
Private Const xlCILime As Long = 43
Private Const xlCIGold As Long = 44
Private Const xlCILightOrange As Long = 45
Private Const xlCIOrange As Long = 46

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error GoTo ws_exit:
    Application.EnableEvents = False
    If Not Intersect(Target, Me.Range("H1:H10")) Is Nothing Then
        With Target
            Select Case LCase(.Value)
                Case "red":         .Interior.ColorIndex = xlCIRed
                Case "blue":        .Interior.ColorIndex = xlCIBlue
                Case "yellow":      .Interior.ColorIndex = xlCIYellow
                'etc
            End Select
        End With
    End If

ws_exit:
    Application.EnableEvents = True
End Sub


0
bob.phillips1 (6510)
8/10/2005 6:42:09 PM
Thanks for this Bob- much appreciated.

I've added the following line of code to reset the formatting back to blank 
if the values entered are removed (so if after a cell has turned red after 
entering "red", the cell returns to blank if the text is deleted):
Case "": .Interior.ColorIndex = xlNone

However, what I really need is for the cell to return to blank if a value 
not specfied in the case statements is entered (so if after a cell has turned 
red after entering "red", the cell returns to blank if any text not in a case 
statement is entered and not just "").

Can you help?

Thanks in advance.

"Bob Phillips" wrote:

> 
> "JonnyCrabb" <JonnyCrabb@discussions.microsoft.com> wrote in message
> news:F6E796A6-B7D8-4A61-97C3-B7D833DFC31F@microsoft.com...
> > Hi Bob,
> >
> > Thanks for your reply. I am a friend of Rob's who is helping him out with
> > this.
> 
> No problems, anyone can join in.
> 
> > Just to understand your code, I presume H1:H10 is the range of values that
> > you are considering, and the "value 1", "value 2" etc are the values that
> you
> > are looking for in that range. Hence the "do something" is what you want
> > doing when a value in your range equals one of your spefied values.
> 
> Correct on all points.
> 
> > What we are trying to achieve is to say if a value in a range is "red",
> then
> > that cell turns red. If the value is "orange", the cell turns orange.
> 
> 
> I thought I did this self same thing a few days ago but I can't find it.
> > As such the "do something" code would look something like:
> >         .ColorIndex = 6
> >         .Pattern = xlSolid
> > but I don't know how to refer to the cell that contains the value to
> change
> > the formatting as appropriate. Can you (or anyone else) help?
> 
> Option Explicit
> 
> Private Const xlCIBlack As Long = 1
> Private Const xlCIWhite As Long = 2
> Private Const xlCIRed As Long = 3
> Private Const xlCIBrightGreen As Long = 4
> Private Const xlCIBlue As Long = 5
> Private Const xlCIYellow As Long = 6
> Private Const xlCIPink As Long = 7
> Private Const xlCITurquoise As Long = 8
> Private Const xlCIDarkRed As Long = 9
> Private Const xlCIGreen As Long = 10
> Private Const xlCIDarkBlue As Long = 11
> Private Const xlCIDarkYellow As Long = 12
> Private Const xlCIViolet As Long = 13
> Private Const xlCITeal As Long = 14
> Private Const xlCIGray25 As Long = 15
> Private Const xlCIGray40 As Long = 16
> Private Const xlCIPaleBlue As Long = 17
> Private Const xlCIPlum As Long = 18
> Private Const xlCILightTurquoise As Long = 20
> Private Const xlCILightBlue As Long = 23
> Private Const xlCIBrown As Long = 30
> Private Const xlCISkyBlue As Long = 33
> Private Const xlCILightGreen As Long = 35
> Private Const xlCILightYellow As Long = 36
> Private Const xlCILavender As Long = 39
> Private Const xlCIAqua As Long = 42
> Private Const xlCILime As Long = 43
> Private Const xlCIGold As Long = 44
> Private Const xlCILightOrange As Long = 45
> Private Const xlCIOrange As Long = 46
> 
> Private Sub Worksheet_Change(ByVal Target As Range)
> 
>     On Error GoTo ws_exit:
>     Application.EnableEvents = False
>     If Not Intersect(Target, Me.Range("H1:H10")) Is Nothing Then
>         With Target
>             Select Case LCase(.Value)
>                 Case "red":         .Interior.ColorIndex = xlCIRed
>                 Case "blue":        .Interior.ColorIndex = xlCIBlue
>                 Case "yellow":      .Interior.ColorIndex = xlCIYellow
>                 'etc
>             End Select
>         End With
>     End If
> 
> ws_exit:
>     Application.EnableEvents = True
> End Sub
> 
> 
> 
0
8/11/2005 11:42:03 AM
Jonny,

Just change that new line to

Case Else: .Interior.ColorIndex = xlColorIndexNone

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"JonnyCrabb" <JonnyCrabb@discussions.microsoft.com> wrote in message
news:B78652BA-9D7E-48A2-BA4A-6DF3D26B7292@microsoft.com...
>
> Thanks for this Bob- much appreciated.
>
> I've added the following line of code to reset the formatting back to
blank
> if the values entered are removed (so if after a cell has turned red after
> entering "red", the cell returns to blank if the text is deleted):
> Case "": .Interior.ColorIndex = xlNone
>
> However, what I really need is for the cell to return to blank if a value
> not specfied in the case statements is entered (so if after a cell has
turned
> red after entering "red", the cell returns to blank if any text not in a
case
> statement is entered and not just "").
>
> Can you help?
>
> Thanks in advance.
>
> "Bob Phillips" wrote:
>
> >
> > "JonnyCrabb" <JonnyCrabb@discussions.microsoft.com> wrote in message
> > news:F6E796A6-B7D8-4A61-97C3-B7D833DFC31F@microsoft.com...
> > > Hi Bob,
> > >
> > > Thanks for your reply. I am a friend of Rob's who is helping him out
with
> > > this.
> >
> > No problems, anyone can join in.
> >
> > > Just to understand your code, I presume H1:H10 is the range of values
that
> > > you are considering, and the "value 1", "value 2" etc are the values
that
> > you
> > > are looking for in that range. Hence the "do something" is what you
want
> > > doing when a value in your range equals one of your spefied values.
> >
> > Correct on all points.
> >
> > > What we are trying to achieve is to say if a value in a range is
"red",
> > then
> > > that cell turns red. If the value is "orange", the cell turns orange.
> >
> >
> > I thought I did this self same thing a few days ago but I can't find it.
> > > As such the "do something" code would look something like:
> > >         .ColorIndex = 6
> > >         .Pattern = xlSolid
> > > but I don't know how to refer to the cell that contains the value to
> > change
> > > the formatting as appropriate. Can you (or anyone else) help?
> >
> > Option Explicit
> >
> > Private Const xlCIBlack As Long = 1
> > Private Const xlCIWhite As Long = 2
> > Private Const xlCIRed As Long = 3
> > Private Const xlCIBrightGreen As Long = 4
> > Private Const xlCIBlue As Long = 5
> > Private Const xlCIYellow As Long = 6
> > Private Const xlCIPink As Long = 7
> > Private Const xlCITurquoise As Long = 8
> > Private Const xlCIDarkRed As Long = 9
> > Private Const xlCIGreen As Long = 10
> > Private Const xlCIDarkBlue As Long = 11
> > Private Const xlCIDarkYellow As Long = 12
> > Private Const xlCIViolet As Long = 13
> > Private Const xlCITeal As Long = 14
> > Private Const xlCIGray25 As Long = 15
> > Private Const xlCIGray40 As Long = 16
> > Private Const xlCIPaleBlue As Long = 17
> > Private Const xlCIPlum As Long = 18
> > Private Const xlCILightTurquoise As Long = 20
> > Private Const xlCILightBlue As Long = 23
> > Private Const xlCIBrown As Long = 30
> > Private Const xlCISkyBlue As Long = 33
> > Private Const xlCILightGreen As Long = 35
> > Private Const xlCILightYellow As Long = 36
> > Private Const xlCILavender As Long = 39
> > Private Const xlCIAqua As Long = 42
> > Private Const xlCILime As Long = 43
> > Private Const xlCIGold As Long = 44
> > Private Const xlCILightOrange As Long = 45
> > Private Const xlCIOrange As Long = 46
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> >
> >     On Error GoTo ws_exit:
> >     Application.EnableEvents = False
> >     If Not Intersect(Target, Me.Range("H1:H10")) Is Nothing Then
> >         With Target
> >             Select Case LCase(.Value)
> >                 Case "red":         .Interior.ColorIndex = xlCIRed
> >                 Case "blue":        .Interior.ColorIndex = xlCIBlue
> >                 Case "yellow":      .Interior.ColorIndex = xlCIYellow
> >                 'etc
> >             End Select
> >         End With
> >     End If
> >
> > ws_exit:
> >     Application.EnableEvents = True
> > End Sub
> >
> >
> >


0
bob.phillips1 (6510)
8/11/2005 1:40:07 PM
Hi Bob,

Thanks for your help so far. Unfortunately I've hit another snag...

It's actually the result of the formula that I want to apply this code to, 
rather than text. So using our example, the output of the formula would be 
"red", as opposed to the text "red" being entered.

Is there a way of editing this code so it can interpret the result of this 
formula rather than the text? I don't know if it's of significance, but the 
formula is actually an array formula.

Thanks in advance for your help.



"Bob Phillips" wrote:

> Jonny,
> 
> Just change that new line to
> 
> Case Else: .Interior.ColorIndex = xlColorIndexNone
> 
> -- 
> 
> HTH
> 
> RP
> (remove nothere from the email address if mailing direct)
> 
> 
> "JonnyCrabb" <JonnyCrabb@discussions.microsoft.com> wrote in message
> news:B78652BA-9D7E-48A2-BA4A-6DF3D26B7292@microsoft.com...
> >
> > Thanks for this Bob- much appreciated.
> >
> > I've added the following line of code to reset the formatting back to
> blank
> > if the values entered are removed (so if after a cell has turned red after
> > entering "red", the cell returns to blank if the text is deleted):
> > Case "": .Interior.ColorIndex = xlNone
> >
> > However, what I really need is for the cell to return to blank if a value
> > not specfied in the case statements is entered (so if after a cell has
> turned
> > red after entering "red", the cell returns to blank if any text not in a
> case
> > statement is entered and not just "").
> >
> > Can you help?
> >
> > Thanks in advance.
> >
> > "Bob Phillips" wrote:
> >
> > >
> > > "JonnyCrabb" <JonnyCrabb@discussions.microsoft.com> wrote in message
> > > news:F6E796A6-B7D8-4A61-97C3-B7D833DFC31F@microsoft.com...
> > > > Hi Bob,
> > > >
> > > > Thanks for your reply. I am a friend of Rob's who is helping him out
> with
> > > > this.
> > >
> > > No problems, anyone can join in.
> > >
> > > > Just to understand your code, I presume H1:H10 is the range of values
> that
> > > > you are considering, and the "value 1", "value 2" etc are the values
> that
> > > you
> > > > are looking for in that range. Hence the "do something" is what you
> want
> > > > doing when a value in your range equals one of your spefied values.
> > >
> > > Correct on all points.
> > >
> > > > What we are trying to achieve is to say if a value in a range is
> "red",
> > > then
> > > > that cell turns red. If the value is "orange", the cell turns orange.
> > >
> > >
> > > I thought I did this self same thing a few days ago but I can't find it.
> > > > As such the "do something" code would look something like:
> > > >         .ColorIndex = 6
> > > >         .Pattern = xlSolid
> > > > but I don't know how to refer to the cell that contains the value to
> > > change
> > > > the formatting as appropriate. Can you (or anyone else) help?
> > >
> > > Option Explicit
> > >
> > > Private Const xlCIBlack As Long = 1
> > > Private Const xlCIWhite As Long = 2
> > > Private Const xlCIRed As Long = 3
> > > Private Const xlCIBrightGreen As Long = 4
> > > Private Const xlCIBlue As Long = 5
> > > Private Const xlCIYellow As Long = 6
> > > Private Const xlCIPink As Long = 7
> > > Private Const xlCITurquoise As Long = 8
> > > Private Const xlCIDarkRed As Long = 9
> > > Private Const xlCIGreen As Long = 10
> > > Private Const xlCIDarkBlue As Long = 11
> > > Private Const xlCIDarkYellow As Long = 12
> > > Private Const xlCIViolet As Long = 13
> > > Private Const xlCITeal As Long = 14
> > > Private Const xlCIGray25 As Long = 15
> > > Private Const xlCIGray40 As Long = 16
> > > Private Const xlCIPaleBlue As Long = 17
> > > Private Const xlCIPlum As Long = 18
> > > Private Const xlCILightTurquoise As Long = 20
> > > Private Const xlCILightBlue As Long = 23
> > > Private Const xlCIBrown As Long = 30
> > > Private Const xlCISkyBlue As Long = 33
> > > Private Const xlCILightGreen As Long = 35
> > > Private Const xlCILightYellow As Long = 36
> > > Private Const xlCILavender As Long = 39
> > > Private Const xlCIAqua As Long = 42
> > > Private Const xlCILime As Long = 43
> > > Private Const xlCIGold As Long = 44
> > > Private Const xlCILightOrange As Long = 45
> > > Private Const xlCIOrange As Long = 46
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > >
> > >     On Error GoTo ws_exit:
> > >     Application.EnableEvents = False
> > >     If Not Intersect(Target, Me.Range("H1:H10")) Is Nothing Then
> > >         With Target
> > >             Select Case LCase(.Value)
> > >                 Case "red":         .Interior.ColorIndex = xlCIRed
> > >                 Case "blue":        .Interior.ColorIndex = xlCIBlue
> > >                 Case "yellow":      .Interior.ColorIndex = xlCIYellow
> > >                 'etc
> > >             End Select
> > >         End With
> > >     End If
> > >
> > > ws_exit:
> > >     Application.EnableEvents = True
> > > End Sub
> > >
> > >
> > >
> 
> 
> 
0
8/16/2005 3:14:03 PM
In addition to the issue below, we also need to only use part of the text 
string to determine what conditional formatting needs to be applied.

Using the example that has been used throughout this thread, we would want:
red carnation to return a red cell
red corvette to return a red cell
blue bottle to return a blue cell 
etc.

Again, thanks in advance for your help.
"JonnyCrabb" wrote:

> Hi Bob,
> 
> Thanks for your help so far. Unfortunately I've hit another snag...
> 
> It's actually the result of the formula that I want to apply this code to, 
> rather than text. So using our example, the output of the formula would be 
> "red", as opposed to the text "red" being entered.
> 
> Is there a way of editing this code so it can interpret the result of this 
> formula rather than the text? I don't know if it's of significance, but the 
> formula is actually an array formula.
> 
> Thanks in advance for your help.
> 
> 
> 
> "Bob Phillips" wrote:
> 
> > Jonny,
> > 
> > Just change that new line to
> > 
> > Case Else: .Interior.ColorIndex = xlColorIndexNone
> > 
> > -- 
> > 
> > HTH
> > 
> > RP
> > (remove nothere from the email address if mailing direct)
> > 
> > 
> > "JonnyCrabb" <JonnyCrabb@discussions.microsoft.com> wrote in message
> > news:B78652BA-9D7E-48A2-BA4A-6DF3D26B7292@microsoft.com...
> > >
> > > Thanks for this Bob- much appreciated.
> > >
> > > I've added the following line of code to reset the formatting back to
> > blank
> > > if the values entered are removed (so if after a cell has turned red after
> > > entering "red", the cell returns to blank if the text is deleted):
> > > Case "": .Interior.ColorIndex = xlNone
> > >
> > > However, what I really need is for the cell to return to blank if a value
> > > not specfied in the case statements is entered (so if after a cell has
> > turned
> > > red after entering "red", the cell returns to blank if any text not in a
> > case
> > > statement is entered and not just "").
> > >
> > > Can you help?
> > >
> > > Thanks in advance.
> > >
> > > "Bob Phillips" wrote:
> > >
> > > >
> > > > "JonnyCrabb" <JonnyCrabb@discussions.microsoft.com> wrote in message
> > > > news:F6E796A6-B7D8-4A61-97C3-B7D833DFC31F@microsoft.com...
> > > > > Hi Bob,
> > > > >
> > > > > Thanks for your reply. I am a friend of Rob's who is helping him out
> > with
> > > > > this.
> > > >
> > > > No problems, anyone can join in.
> > > >
> > > > > Just to understand your code, I presume H1:H10 is the range of values
> > that
> > > > > you are considering, and the "value 1", "value 2" etc are the values
> > that
> > > > you
> > > > > are looking for in that range. Hence the "do something" is what you
> > want
> > > > > doing when a value in your range equals one of your spefied values.
> > > >
> > > > Correct on all points.
> > > >
> > > > > What we are trying to achieve is to say if a value in a range is
> > "red",
> > > > then
> > > > > that cell turns red. If the value is "orange", the cell turns orange.
> > > >
> > > >
> > > > I thought I did this self same thing a few days ago but I can't find it.
> > > > > As such the "do something" code would look something like:
> > > > >         .ColorIndex = 6
> > > > >         .Pattern = xlSolid
> > > > > but I don't know how to refer to the cell that contains the value to
> > > > change
> > > > > the formatting as appropriate. Can you (or anyone else) help?
> > > >
> > > > Option Explicit
> > > >
> > > > Private Const xlCIBlack As Long = 1
> > > > Private Const xlCIWhite As Long = 2
> > > > Private Const xlCIRed As Long = 3
> > > > Private Const xlCIBrightGreen As Long = 4
> > > > Private Const xlCIBlue As Long = 5
> > > > Private Const xlCIYellow As Long = 6
> > > > Private Const xlCIPink As Long = 7
> > > > Private Const xlCITurquoise As Long = 8
> > > > Private Const xlCIDarkRed As Long = 9
> > > > Private Const xlCIGreen As Long = 10
> > > > Private Const xlCIDarkBlue As Long = 11
> > > > Private Const xlCIDarkYellow As Long = 12
> > > > Private Const xlCIViolet As Long = 13
> > > > Private Const xlCITeal As Long = 14
> > > > Private Const xlCIGray25 As Long = 15
> > > > Private Const xlCIGray40 As Long = 16
> > > > Private Const xlCIPaleBlue As Long = 17
> > > > Private Const xlCIPlum As Long = 18
> > > > Private Const xlCILightTurquoise As Long = 20
> > > > Private Const xlCILightBlue As Long = 23
> > > > Private Const xlCIBrown As Long = 30
> > > > Private Const xlCISkyBlue As Long = 33
> > > > Private Const xlCILightGreen As Long = 35
> > > > Private Const xlCILightYellow As Long = 36
> > > > Private Const xlCILavender As Long = 39
> > > > Private Const xlCIAqua As Long = 42
> > > > Private Const xlCILime As Long = 43
> > > > Private Const xlCIGold As Long = 44
> > > > Private Const xlCILightOrange As Long = 45
> > > > Private Const xlCIOrange As Long = 46
> > > >
> > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > >
> > > >     On Error GoTo ws_exit:
> > > >     Application.EnableEvents = False
> > > >     If Not Intersect(Target, Me.Range("H1:H10")) Is Nothing Then
> > > >         With Target
> > > >             Select Case LCase(.Value)
> > > >                 Case "red":         .Interior.ColorIndex = xlCIRed
> > > >                 Case "blue":        .Interior.ColorIndex = xlCIBlue
> > > >                 Case "yellow":      .Interior.ColorIndex = xlCIYellow
> > > >                 'etc
> > > >             End Select
> > > >         End With
> > > >     End If
> > > >
> > > > ws_exit:
> > > >     Application.EnableEvents = True
> > > > End Sub
> > > >
> > > >
> > > >
> > 
> > 
> > 
0
8/17/2005 10:47:01 AM
I've found the answer to question 2. Entering the following code:

Select Case LCase(Split(.Value)(0))

in place of:

Select Case LCase(.Value)

Enables you to trigger the conditional formatting off the first word only.

I'm still looking for the answer to question 1...

Thanks in anticipation...


"JonnyCrabb" wrote:

> In addition to the issue below, we also need to only use part of the text 
> string to determine what conditional formatting needs to be applied.
> 
> Using the example that has been used throughout this thread, we would want:
> red carnation to return a red cell
> red corvette to return a red cell
> blue bottle to return a blue cell 
> etc.
> 
> Again, thanks in advance for your help.
> "JonnyCrabb" wrote:
> 
> > Hi Bob,
> > 
> > Thanks for your help so far. Unfortunately I've hit another snag...
> > 
> > It's actually the result of the formula that I want to apply this code to, 
> > rather than text. So using our example, the output of the formula would be 
> > "red", as opposed to the text "red" being entered.
> > 
> > Is there a way of editing this code so it can interpret the result of this 
> > formula rather than the text? I don't know if it's of significance, but the 
> > formula is actually an array formula.
> > 
> > Thanks in advance for your help.
> > 
> > 
> > 
> > "Bob Phillips" wrote:
> > 
> > > Jonny,
> > > 
> > > Just change that new line to
> > > 
> > > Case Else: .Interior.ColorIndex = xlColorIndexNone
> > > 
> > > -- 
> > > 
> > > HTH
> > > 
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > > 
> > > 
> > > "JonnyCrabb" <JonnyCrabb@discussions.microsoft.com> wrote in message
> > > news:B78652BA-9D7E-48A2-BA4A-6DF3D26B7292@microsoft.com...
> > > >
> > > > Thanks for this Bob- much appreciated.
> > > >
> > > > I've added the following line of code to reset the formatting back to
> > > blank
> > > > if the values entered are removed (so if after a cell has turned red after
> > > > entering "red", the cell returns to blank if the text is deleted):
> > > > Case "": .Interior.ColorIndex = xlNone
> > > >
> > > > However, what I really need is for the cell to return to blank if a value
> > > > not specfied in the case statements is entered (so if after a cell has
> > > turned
> > > > red after entering "red", the cell returns to blank if any text not in a
> > > case
> > > > statement is entered and not just "").
> > > >
> > > > Can you help?
> > > >
> > > > Thanks in advance.
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > >
> > > > > "JonnyCrabb" <JonnyCrabb@discussions.microsoft.com> wrote in message
> > > > > news:F6E796A6-B7D8-4A61-97C3-B7D833DFC31F@microsoft.com...
> > > > > > Hi Bob,
> > > > > >
> > > > > > Thanks for your reply. I am a friend of Rob's who is helping him out
> > > with
> > > > > > this.
> > > > >
> > > > > No problems, anyone can join in.
> > > > >
> > > > > > Just to understand your code, I presume H1:H10 is the range of values
> > > that
> > > > > > you are considering, and the "value 1", "value 2" etc are the values
> > > that
> > > > > you
> > > > > > are looking for in that range. Hence the "do something" is what you
> > > want
> > > > > > doing when a value in your range equals one of your spefied values.
> > > > >
> > > > > Correct on all points.
> > > > >
> > > > > > What we are trying to achieve is to say if a value in a range is
> > > "red",
> > > > > then
> > > > > > that cell turns red. If the value is "orange", the cell turns orange.
> > > > >
> > > > >
> > > > > I thought I did this self same thing a few days ago but I can't find it.
> > > > > > As such the "do something" code would look something like:
> > > > > >         .ColorIndex = 6
> > > > > >         .Pattern = xlSolid
> > > > > > but I don't know how to refer to the cell that contains the value to
> > > > > change
> > > > > > the formatting as appropriate. Can you (or anyone else) help?
> > > > >
> > > > > Option Explicit
> > > > >
> > > > > Private Const xlCIBlack As Long = 1
> > > > > Private Const xlCIWhite As Long = 2
> > > > > Private Const xlCIRed As Long = 3
> > > > > Private Const xlCIBrightGreen As Long = 4
> > > > > Private Const xlCIBlue As Long = 5
> > > > > Private Const xlCIYellow As Long = 6
> > > > > Private Const xlCIPink As Long = 7
> > > > > Private Const xlCITurquoise As Long = 8
> > > > > Private Const xlCIDarkRed As Long = 9
> > > > > Private Const xlCIGreen As Long = 10
> > > > > Private Const xlCIDarkBlue As Long = 11
> > > > > Private Const xlCIDarkYellow As Long = 12
> > > > > Private Const xlCIViolet As Long = 13
> > > > > Private Const xlCITeal As Long = 14
> > > > > Private Const xlCIGray25 As Long = 15
> > > > > Private Const xlCIGray40 As Long = 16
> > > > > Private Const xlCIPaleBlue As Long = 17
> > > > > Private Const xlCIPlum As Long = 18
> > > > > Private Const xlCILightTurquoise As Long = 20
> > > > > Private Const xlCILightBlue As Long = 23
> > > > > Private Const xlCIBrown As Long = 30
> > > > > Private Const xlCISkyBlue As Long = 33
> > > > > Private Const xlCILightGreen As Long = 35
> > > > > Private Const xlCILightYellow As Long = 36
> > > > > Private Const xlCILavender As Long = 39
> > > > > Private Const xlCIAqua As Long = 42
> > > > > Private Const xlCILime As Long = 43
> > > > > Private Const xlCIGold As Long = 44
> > > > > Private Const xlCILightOrange As Long = 45
> > > > > Private Const xlCIOrange As Long = 46
> > > > >
> > > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > >
> > > > >     On Error GoTo ws_exit:
> > > > >     Application.EnableEvents = False
> > > > >     If Not Intersect(Target, Me.Range("H1:H10")) Is Nothing Then
> > > > >         With Target
> > > > >             Select Case LCase(.Value)
> > > > >                 Case "red":         .Interior.ColorIndex = xlCIRed
> > > > >                 Case "blue":        .Interior.ColorIndex = xlCIBlue
> > > > >                 Case "yellow":      .Interior.ColorIndex = xlCIYellow
> > > > >                 'etc
> > > > >             End Select
> > > > >         End With
> > > > >     End If
> > > > >
> > > > > ws_exit:
> > > > >     Application.EnableEvents = True
> > > > > End Sub
> > > > >
> > > > >
> > > > >
> > > 
> > > 
> > > 
0
8/17/2005 3:02:09 PM
Reply:

Similar Artilces:

Custom format #3
To format a custom format for credit cards I tried 0000-0000-0000-0000 and it looks good like it should work. Somehow the last digit changes to 0 no matter what number I put in. Yes have tried it several times, on sheet the church sent me and again trying it on Excel 2003. Weird problem always ends in 0 ?? On Mon, 25 May 2009 12:56:59 -0400, "John McCabe" <jlmccabes@cfl.rr.com> wrote: >To format a custom format for credit cards I tried > 0000-0000-0000-0000 and it looks good like it should work. >Somehow the last digit changes to 0 no...

3 leaflets in landscape on an A4 page. Any suggestions?
Microsoft has wonderful templates. Love them. Can't find a leaflet back and front which is on an A4 page which requires only 2 slices to make it in to 3 leflets. Max usage of paper, minimum cost and minimum environmental cost. Start with something close and modify it. Or remember that not everything needs to be started with a template. Creativity goes a long way. -- JoAnn Paules MVP Microsoft [Publisher] "Rosemarie" <Rosemarie@discussions.microsoft.com> wrote in message news:777EFC05-28DA-45A1-A6FA-687400391573@microsoft.com... > Microsoft has wonderful...

Format Merge Field
I have a mergefield that can either be a whole number (i.e. 7), a number with a decimal (i.e. 2.14), or a text field (i.e. 13,14,15 & 17). I want to format them so that each type comes out as it appears above (7, 2.14, and 13,14,15, & 17). Is there a way to format a merge field to accomodate this? Thanks for your help. Hi C Huff, You can't do this by formatting the mergefield. You may be able to do by ensuring the first 8 rows of data for the field concerned have text strings (eg 13,14,15 & 17). If that doesn't work and your decimal values are all of the...

WU hangs on 3 of 3
Fresh SBS 2008 install, installed SP2 afterwards, reboot all ok, then executed windows update with all criticals, it downloads and installs succesfully. After reboot it starts windows with "configuring updates x of 3, don't turn off your computer" and the server hangs there on the "3 out of 3" on 0%, there's no lan or disk activity at all and the progress circle just keeps running. This already happened yesterday with a canceled WU(before installing SP2) and we had to format and reinstall thanks to this problem because safe mode worked but after th...

mail delivery #3
i just install new echange 2003 ent. to existing 2000 ad where is exchange 2000 standard. In my test enviroment all mail delivery succees in new server, but when i do exactly same in production, those one's which are in exchange 2003 they can't get any mails from "old server" or outside our corporate. They can send mail to users which mailboxes are inside our corporate, and they received mails from new servers. ...

conditional formating 12-22-09
Excel Users, Thanks for the help. I would like to apply a conditional format to A1 if A2 meets a criteria. For example if A2 is 100%, I would like A1 to become bold. I can't seem to figure out this formula. Can you help? Thanks Jim Jim, The CF formula is =A2=1 and then select Bold font Mike "Jim" wrote: > Excel Users, > > Thanks for the help. I would like to apply a conditional format to A1 if A2 > meets a criteria. > > For example if A2 is 100%, I would like A1 to become bold. I can't seem to > figure out ...

ClearContents with condition
Hello, Using Excel XP. I have the following range in Sheet1: Range E1: H downwards (b) = Blank cell DESC CODE PURCHASE1 PURCHASE2 Widget S101 (b) $100.00 Widget S102 (b) (b) Widget S103 $250.00 (b) Widget S104 (b) (b) Widget S105 $325.00 (b) I am trying to devise a macro to clear the description cell (column E) and the code cell (column F) if both cells in columns G and H are blank. So, I end up with: DESC CODE PURCHASE1 PURCHASE2 Widget S101 (b) $100.00 (b) (b) (b) (b) Widget S103 $250.00 (b) (b) (b) ...

Windows Sharepoint Services 3.0 Migration.
Hello, I am in a bit of trouble here and need some help. We have a Windows 2003 Standard server (X86) running SQL 2005 and SharePoint Services 3.0. We need to migrate the SharePoint Services 3.0 to a Windows 2008 R2 server running SQL 2005 X64. The server name will be different than the original server. Since we have the server name, the operating system version and the processor architecture changing I am looking for clear instructions on how to migrate the SharePoint Application. I read a lot websites including this one here http://technet.microsoft.com/en-us/library/cc5...

Duplicate look_up values in VLOOKUP #3
Hi all, I've got a VLOOKUP formula functioning in the usual way. However, sometimes the look_up value is duplicated. When this occurs VLOOKUP always returns the first instance of the entry. Is there a means to get Excel to ignore entries it has already returned an entry for? The VLOOKUP is part of a bigger formula, being: =IF(AND('Dependency Log'!$A2=Matrix!$B3, 'Dependency Log'!$E2=Matrix!D$2),(VLOOKUP($B3,look_up,2,FALSE) ),"") Any ideas? TIA, SamuelT -- SamuelT ------------------------------------------------------------------------ SamuelT's Prof...

math #3
How do I get the average of a group of years. Like 1999, 1998, 2000, etc. I think you need to provide much more detail to get a useful reply. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Bill" <Bill@discussions.microsoft.com> wrote in message news:044868EC-8884-4E40-9F93-EE9C5EFABCFA@microsoft.com... > How do I get the average of a group of years. Like 1999, 1998, > 2000, etc. maybe something like =AVERAGE(ROW(INDIRECT("1998:2005"))) as an array formula, so commit with Ctrl-Shift-Enter -- ...

Upgrade #3
Hi Folks; If I am running MS Office 2000 on my XP Home Edition box can I upgrade just the Outlook portion of Office to Outlook 2003 or Outlook XP ????????????????? TIA Later, Malcolm On Sat, 19 Nov 2005 07:38:57 GMT, Malcolm <malcolm34465@yahoo.com> wrote: > >If I am running MS Office 2000 on my XP Home Edition box can I upgrade >just the Outlook portion of Office to Outlook 2003 or Outlook XP >????????????????? > Sure. Just know that you will not be able to use Word as your message editor in Outlook and you will not be able to use Word in a mail merge. -- <<&...

Format of email address
As I look at individual contacts, I notice that in the email field, most of them have an actul email address, something like username@domainname.com. But in a few of them, the email address field is the same as the name field, but underlined. For example, one of my contacts is "John Doe". His email address is "jdoe@hotmail.com". If I open his contact record, the full name field shows "John Doe". The email field shows "John Doe" (underlined). If I double click on the email field, an "email properties" panel pops up showing the actual email...

CCheckListBox #3
Dear Charles As you suggested to my question posted in ADO.Net news group I added the implimentation of the Create() function of CCheckListBox class. but it is still giving following error: error LNK2001: unresolved external symbol "public: virtual struct CRuntimeClass * __thiscall CMyCheckListBox::GetRuntimeClass(void)const " (?GetRuntimeClass@CMyCheckListBox@@UBEPAUCRuntimeClass@@XZ) Thanks Manjree "Manjree Garg" <garg@newsgroup.nospam> ha scritto nel messaggio news:F59BE84D-88CA-4994-8F1F-D6888AD8D45E@microsoft.com... > As you suggested to my qu...

more than 3 conditional formating in excel
Hi I am new to conditional formating in Excel. In row 2 I need to enter nos. between 1-5. I want each color to have a particular color. I have managed to do 4 (3 with conditional formating and the 4th retaining the default color). Is there a way i can do all 5 colors? Thanks You need to use VBA. Try this:- Private Sub Worksheet_Calculate() 'Code must be placed in the codemodule of the actual sheet you are working with. Dim oCell As Range For Each oCell In Range("A1:A20") Select Case oCell.Value Case Is < 1 oCell.Interior.Colo...

OWA #3
Hi , we have the following setup and would like advice as to what others are doing. Internet-->Router->Cisco Pix-->(Internal Network->Exchange Srv) We would like to publish our Outlook Web Access the most secure way possible. We do not want to put a Windows 2k box in the DMZ or outside or Network to use as a front end server. Could we do the following: Internet-->Router->Cisco Pix with port 443 mapped to FrontEndSrv->(Internal Network->FrontEndSrv Exchange with tight security providing OWA for Internal Exchange server)...and of course ssl cert on the Exch box....

Event id 1000 #3
Hello, I have office xp installed on pc with profing tools and sometimes when using outlook reply message, crash and open word with recouvering document. the error on event viewer is event id 1000 source microsoft office 10 . Please can help me! Thanks in advance. ...

Macro Help #3
I turn to you guru's for help with a macro question. I have to worksheets in my workbook. Both have a coulmn that has a 6 digit number in each cell (an invoice number) What I need to do is have a macro that starts with the first invoice # in sheet 1, searches for it in sheet 2. If it finds it, delete that row from Sheet 2. Then it will look at the 2nd invoice number on sheet 1, Search from it in Sheet 2 If it finds it, delete that row from Sheet 2. etc, etc, etc so, my question to you is..... How? Modify this to suit. Sub Deletematches() On Error Resume Next For Each cel In [c14:...

Date Format #14
In excel 2003 we used to type the date without / or - When we do that in Excel 2007, we get a lot of hashes (####). How can we get it to behave the same as Excel 2007? If you typed the dates in without slashes or dashes in xl2003 (like 01082008), then you had another program running that would parse this entry into a date. Excel won't do this on its own. You may want to go back and find what addin you were using in xl2003 and install it in xl2007. It may have been Chip Pearson's: http://cpearson.com/excel/DateTimeEntry.htm Gabriel van Rensburg wrote: > > In excel 2003 we us...

Applying different formatting in the text box that has conditional
Is it possible to apply different formatting styles in a textbox that has reference to multiple fields and formulas? Thank you Yes. Show what you are putting in the text box and how you would want it formatted. -- KARL DEWEY Build a little - Test a little "olesja" wrote: > Is it possible to apply different formatting styles in a textbox that has > reference to multiple fields and formulas? Thank you olesja wrote: >Is it possible to apply different formatting styles in a textbox that has >reference to multiple fields and formulas? It's probably doable in a ...

Disclaimer #3
Hi We run Win2003, Exchange2003 and I would like to add a disclaimer on all outgoing emails. Is there an other way then buying a Add-on tool? If not what is good tool? Thanks Roman Sure. You can take a look at this: http://support.microsoft.com/default.aspx?scid=kb;EN-US;317680 or this http://support.microsoft.com/default.aspx?scid=kb;EN-US;317327 -- Martin Blackstone MVP - Exchange http://www.swinc.com/resource/exchange.htm http://www.swinc.com/resource/e2kfaq_appxc.htm "Roman" <roman@schwabbrothers.com> wrote in message news:OUj1pkBoEHA.4008@TK2MSFTNGP14.phx.gbl....

VBA to change format of a linked cell
I apologize if this is really simple, but I've been searching around on the web and haven't found what I am looking for.... Maybe I am not searching for the right thing..... I would like to know the VBA syntax that I could use to change the font format of a linked cell to be the same as the cell it is linked to... A simple example: on sheet2, in cell b13, is the value 2. It is colored blue and is bolded. on sheet2, in cell b47, is the value 5. it is formatted in the default manner cell A1, on the sheet named sheet1, contains the formula ='sheet2!b13' cell A2, on the sheet ...

Conditional Value
Column A = Job Status Column B = Date Completed What I would like to have is if Column A = Done and Column B has no value, I need to have a warning or a pop up of some kind stating that Column B needs to have a date value when column A = Done. Thank you in advance. in a separate column? =IF(A2="Done",IF(B2="","Date required",""),"") -- HTH RP (remove nothere from the email address if mailing direct) "Michael S." <MichaelS@discussions.microsoft.com> wrote in message news:BF4DD94D-F029-4F4A-A614-86F898E447E9@microsoft....

how do i make 1/3 look like a fraction
i can get 1/4 and 1/2 to look like a fraction but the rest (1/3 1/8 1/16) don't happen There are a couple of possibilities - see: http://www.gmayor.com/createfraction.htm or http://www.word.mvps.org/FAQs/Formatting/CreateFraction.htm -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<...

Resource booking #3
Hi everyone, I can't book an resorce , it always says that it is recurring and I can't change recurrence. Thanx in advance. And is the meeting you are organizing a recurring meeting? A bit more details would be nice here, such as how you have set up the resource mailboxes, what version of Exchange, and so forth. -- Ben Winzenz Exchange MVP MessageOne Read my blog! http://winzenz.blogspot.com http://feeds.feedburner.com/winzenz (RSS Feed) "Peter" <Peter@discussions.microsoft.com> wrote in message news:03C60C8B-8E7E-4A6A-9E68-1EC160315027@microsoft.com... > H...

Free Busy information #3
Free busy information for Exchange 5.5 not visible in Exchange 2003 in mix mode. Exchanhe 2003 free busy information not visible in different admin groups either. Why is this happening?? You need to configure the free/busy folder to replicate to the different admin groups. For more information and some topology descriptions, see "Working with the Exchange Server 2003 Store" (http://go.microsoft.com/fwlink/?LinkId=33049). Chapter 6 deals with free/busy. -- Teresa Appelgate Exchange User Education ****** Disclaimers ****** This posting is provided "AS IS" with no warran...