Need more CF capacity

[Phil Hageman]

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

 

[Dave Ramage]

Phil,

You'll need to use some VBA code to do this. Add this to
the worksheet module of the worksheet in question (right
click tab and select View Code)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strTemp As String
If Not IsEmpty(Target) Then
strTemp = Right(Target.Cells(1).Value, 2)
Select Case strTemp
Case "ot"
Target.Interior.ColorIndex = 4
Target.Font.Bold = True
Case "la"
Target.Interior.ColorIndex = 13
Target.Font.Bold = True
Case "ls"
Target.Interior.ColorIndex = 10
Target.Font.Bold = True
Case "ce"
Target.Interior.ColorIndex = 7
Target.Font.Bold = True
Case "ct"
Target.Interior.ColorIndex = 8
Target.Font.Bold = True
Case Else
Target.Interior.ColorIndex = xlColorIndexNone
Target.Font.Bold = False
End Select
End If
End Sub

Look up ColorIndex in VBA help to see which number
represents each color.

Cheers,
Dave

 

[Phil Hageman]

The code will act when the user makes the entry, and the
cell in which they make the entry is formatted.

 

[Phil Hageman]

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.

 

[Tom Ogilvy]

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

 

[Phil Hageman]

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?