Need more CF capacity

  • Thread starter Thread starter Phil Hageman
  • Start date Start date
P

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
 
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
 
The code will act when the user makes the entry, and the
cell in which they make the entry is formatted.
 
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.
 
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
 
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?
 
Back
Top