Format cells based on text string contains

[Also]

Had a good search but have to ask sadly.

Using a Macro I have generated several columns of data.
For columns F, H, I and J I want to in effect conditional format based on 4
conditions- the 3 limit stumps me!

The idea is that all cells in range which have "1st" are on colour (with
different text colour), "2nd" and so on- BUT there is more than just "1st"
etc in the cell.

Using conditional formatting I've got the result below, but of course can't
add a 4th! Any ideas/help please.
I've self taught myself Macros but I can only go so far.

' F Column
Columns("F:F").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ISNUMBER(SEARCH(""1st"",$F1))"
Selection.FormatConditions(1).Font.ColorIndex = 2
Selection.FormatConditions(1).Interior.ColorIndex = 10
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ISNUMBER(SEARCH(""2nd"",$F1))"
Selection.FormatConditions(2).Interior.ColorIndex = 43
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ISNUMBER(SEARCH(""3rd"",$F1))"
Selection.FormatConditions(3).Font.ColorIndex = xlAutomatic
Selection.FormatConditions(3).Interior.ColorIndex = 45

 

[Bernard Liengme]

Have you looked at:
http://www.ozgrid.com/VBA/excel-conditional-formatting-limit.htm
best wishes

 

[CLR]

Here's a little ditty that someone in the group gave me awhile
back.........perhaps you can modify it to fit your needs.........

Private Sub Worksheet_Change(ByVal Target As Range)
' this has to go in your worksheet module:
' right-click on your tab name and choose 'code' in the context menu
' paste your code in the appearing editor window
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:A5")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
Select Case .Value
Case Is = Range("e1").Value: .Interior.ColorIndex = 4 'green
Case Is = Range("e2").Value: .Interior.ColorIndex = 5 'dark blue
Case Is = Range("e3").Value: .Interior.ColorIndex = 6 'yellow
Case Is = Range("e4").Value: .Interior.ColorIndex = 7 'magenta
Case Is = Range("e5").Value: .Interior.ColorIndex = 8 'light blue
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub

Vaya con Dios,
Chuck, CABGx3

 

[Mike H]

Might this be any good?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim interiorcolor As Integer

If Not Intersect(Target, Range("f:f")) Is Nothing Then
Select Case Target
Case "1st"
interiorcolor = 2
Case "2nd"
interiorcolor = 10
Case "3rd"
interiorcolor = 43
Case "4th"
interiorcolor = 45
Case Else
'Do Nothing
End Select

Target.Interior.ColorIndex = interiorcolor
End If

End Sub

Right click the sheet tab, view code and paste it in.

Mike

 

[Also]

OK,
Seems to make sense, however, I need to do it as part of a Macro that I can
call i.e.:

Sub Name()

I don't know how to convert the scripts starting:

Private Sub Worksheet_Change(ByVal Target As Range)

Currently the Macro pulls some filtered data from one sheet and pastes it
into a new sheet, does some formatting/rearranging etc. So this is actually
the last step in a process.

Thanks all for the help so far.

 

[Gord Dibben]

Not event code but just a straight macro to run after your pasting and
rearranging.


Sub namecolor()
Dim Num As Long
Dim rng As Range
Dim vRngInput As Range
Set vRngInput = ActiveSheet.Range("F1:J1000")

On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case UCase(rng.Value)
Case Is = "A": Num = 10 'green
Case Is = "B": Num = 1 'black
Case Is = "C": Num = 5 'blue
Case Is = "D": Num = 7 'magenta
Case Is = "E": Num = 46 'orange
Case Is = "F": Num = 3 'red
Case Else
Num = 0
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
endit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

 

[Also]

Tried adding that in and it err... did nothing- I did look through it but
couldn't spot the error.
This is with using just "1st" in the cell as well. Part of the problem is
that there is more than just "1st" in the cell so it needs to search within
the cell.

 

[Also]

On further analysis- I can't figure out how to get it to do it for where the
flag is anywhere in a text string in the cell.
e.g. "1st" in a cell which say something like "23 1st Quartile"
I tried copy pasting and using variations on the ISNUMBER(SEARCH( combo
which I use for conditional formatting but it doesn't like it.

I am hoping to use a Macro to paste this Macro in to a new sheet, but it's
not looking so great on that front either.

Allan