more than 3 conditions in conditional formatting - possible?

[rob curtis]

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

[Kay]

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

[Bob Phillips]

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 [email protected]> wrote in message
news:[email protected]...
> 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

[JonnyCrabb]

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 [email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[JonnyCrabb]

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

[Bob Phillips]

Yes

--

HTH

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


"JonnyCrabb" <[email protected]> wrote in message
news:[email protected]...
> 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

[Bob Phillips]

"JonnyCrabb" <[email protected]> wrote in message
news:[email protected]...
> 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

[JonnyCrabb]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

[Bob Phillips]

Jonny,

Just change that new line to

Case Else: .Interior.ColorIndex = xlColorIndexNone

--

HTH

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


"JonnyCrabb" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >

[JonnyCrabb]

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" <[email protected]> wrote in message
> news:[email protected]...
> >
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > >
> > >
> > >
>
>
>

[JonnyCrabb]

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" <[email protected]> wrote in message
> > news:[email protected]...
> > >
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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
> > > >
> > > >
> > > >
> >
> >
> >

[JonnyCrabb]

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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >
> > > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >