I see what you mean. Can you recommend a course of
action? Is there a way to duplicate the CF code - and
expand it to allow more than three CFs?
-----Original Message-----
NO.
as written it would change any other cell not containing
one of the values
to no background color and not bold. That was there to
handle the case
where you delete a cell that was formatted - however, if
there are other
cells you want untouched, then you can't have the Case
Else and least not
without further checks.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
For Each cell In Target
Select Case Right(LCase(cell.Value), 2)
Case "ot"
cell.Interior.ColorIndex = 35
cell.Font.Bold = True
Case "la"
cell.Interior.ColorIndex = 39
cell.Font.Bold = True
Case "ls"
cell.Interior.ColorIndex = 4
cell.Font.Bold = True
Case "ct"
cell.Interior.ColorIndex = 5
cell.Font.Bold = True
Case "ce"
cell.Interior.ColorIndex = 6
cell.Font.Bold = True
' Case Else
' cell.Interior.ColorIndex = xlNone
' cell.Font.Bold = False
End Select
Next

End Sub
--
Regards,
Tom Ogilvy


"Phil Hageman" wrote in message
...
Tom, when a user enters, say, ot, as the right-most two
characters, only that cell is formatted according to the
code, correct? Any other cell will be unaffected? (code
for other cases notwithstanding). The worksheet has a
variety of other data cells and cell formats.
-----Original Message-----
Dave Ramage basically produced the same code as I and
packaged it all under
the change event which is what you want.

but for my code it would be:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
For Each cell In Target
Select Case Right(LCase(cell.Value), 2)
Case "ot"
cell.Interior.ColorIndex = 35
cell.Font.Bold = True
Case "la"
cell.Interior.ColorIndex = 39
cell.Font.Bold = True
Case "ls"
cell.Interior.ColorIndex = 4
cell.Font.Bold = True
Case "ct"
cell.Interior.ColorIndex = 5
cell.Font.Bold = True
Case "ce"
cell.Interior.ColorIndex = 6
cell.Font.Bold = True
Case Else
cell.Interior.ColorIndex = xlNone
cell.Font.Bold = False
End Select
Next

End Sub

Remove any conditional formatting. You can check the
colorindex property
in VBA help for mapping a color to the color index or
run
code like this on
a blank sheet

Sub aa_tester11()
For i = 0 To 56
Cells(i + 1, 1).Interior.ColorIndex = i
Cells(i + 1, 2).Value = i
Next

End Sub



--
Regards,
Tom Ogilvy


"Phil Hageman" wrote in message
...
The code will act when the user makes the entry, and
the
cell in which they make the entry is formatted.
-----Original Message-----
When do you want it to happen. Will the user enter
one
of those values
(Worksheet_change event). Will they be produced
through
a formula
(Calculate Event).

Select Case Right(lcase(Cell.value),2)
Case "ot"
Cell.Interior.ColorIndex = 35
cell.Font.Bold = True
Case "la"
cell.Interior.ColorIndex = 39
cell.Font.Bold = True
Case "ls"
cell.Interior.ColorIndex = 4
cell.Font.Bold = True Case "ce"
Case "ct"
cell.Interior.ColorIndex = 5
cell.Font.Bold = True
Case "ce"
cell.Interior.ColorIndex = 6
cell.Font.Bold = True
Case Else
cell.Interior.ColorIndex = xlNone
cell.Font.Bold = False
End Select


If the Change event, you can do

cell = Target

If the Calculate event

set rng = me.cells.specialcells
(xlFormulas,xlTextValues)
for each cell in rng


Next

--
Regards,
Tom Ogilvy

"Phil Hageman" wrote in message
...
The conditional formatting limit is three
conditions. I
am using all three, as follows:
formula conditional format
=RIGHT(G38,2)="ot font bold, background lt.
green
=RIGHT(G38,2)="la" font bold, background
lavander
=RIGHT(G38,2)="ls" font bold, background
green

These CFs are good for any cell in the worksheet.

I need additional CF formatting on this worksheet,
for
example, I need to add "ce" and "ct". What would
the
code
be, and where would I put it?

Thanks, Phil



.



.



.