Conditional formatting

[Goldie]

Is it possible to use a table of results to determine the formatting results
in conditional formatting?
I am wanting to use more than the allowed 3 conditions to format a series of
cells.
Say for instance I wanted the cell to format if the conditions were :
H
HN
..H
..NH
3.5/H
..3.5/H
and so forth
The list I have has 10 conditions in it and would like to use all 10 but as
I am only allowed to use 3 I am struggling.

Any help is appreciated.

Goldie

 

[Don Guillett]

Here is one I sent in response to a similar post. Modify to suit
right click on sheet tab>view code>insert this>SAVE

Private Sub Worksheet_Change(ByVal Target As Range)
Set cRange = Intersect(Range("y174:y180"), Range(Target(1).Address))
If cRange Is Nothing Then Exit Sub
On Error GoTo fixit
Application.EnableEvents = False
Select Case Target
Case 1
Target.Interior.ColorIndex = 3
Case 2
Target.Interior.ColorIndex = 28
Case 3
Target.Interior.ColorIndex = 27
Case 4
Target.Interior.ColorIndex = 41
End Select
Target = "*" & Target
fixit:
Application.EnableEvents = True

 

[Dave Peterson]

You could use a worksheet event (either _change or _calculate depending on how
the cell gets updated). If you type it in, then _change. If the cell contains
a formula, then _calculate.

I chose to use _change for this example. I also guessed that the data was in
column A and I chose colors just by numeric sequence. You can record a macro
when you format the cell and steal the code you want from there.

Right click on the worksheet tab that should have this behavior and select view
code. Paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub

With Target
Select Case LCase(.Value)
Case Is = "h": .Interior.ColorIndex = 5
Case Is = "hn": .Interior.ColorIndex = 6
Case Is = ".h": .Interior.ColorIndex = 7
Case Is = ".nh": .Interior.ColorIndex = 8
Case Is = "3.5/": .Interior.ColorIndex = 9
Case Is = ".3.5/h": .Interior.ColorIndex = 10
Case Else
.Interior.ColorIndex = xlNone
End Select
End With

End Sub

Back to excel and try it out. (I didn't care about upper/lower case, either.)

 

[J.E. McGimpsey]

You'll need to use an event macro.

The archives:

http://google.com/advanced_group_search?q=group:*excel*

have hundreds of threads explaining how.

 

[Goldie]

Thanks to the first two answers they were really helpful and worked a treat.

Goldie

 

[kpg]

Yes.

 

[David McRitchie]

I don't think you have your Outlook Express set up properly to see
threads. View, Current View, [x] Group Messages by Conversation
-- or perhaps you replied to the original message instead of a specific
message within the thread.

I have a page on Event Macros that may be of further help to some.
http://www.mvps.org/dmcritchie/excel/event.htm#case
but best read from the top of the webpage.
--