Format cells based on text string contains
 

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

Format cells based on text string contains
 

Have you looked at:
http://www.ozgrid.com/VBA/excel-cond...ting-limit.htm
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Also" wrote in message
...
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

Format cells based on text string contains
 

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



"Also" wrote:

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

Format cells based on text string contains
 

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" wrote:

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

Format cells based on text string contains
 

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.

Format cells based on text string contains
 

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

On Thu, 10 May 2007 09:31:01 -0700, Also wrote:

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.

Format cells based on text string contains
 

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.

Format cells based on text string contains
 

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