more than 3 conditions in conditional formatting - possible?

[Guest]

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

 

[Guest]

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

Try out www.xlDynamic.com

 

[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)

 

[Guest]

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?

 

[Guest]

Thanks for this Kay,

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

 

[Bob Phillips]

Yes

--

HTH

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

 

[Bob Phillips]

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

 

[Guest]

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]

Jonny,

Just change that new line to

Case Else: .Interior.ColorIndex = xlColorIndexNone

--

HTH

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

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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...