Wildcard

[cadence]

Hello all not to sure on the use of wildcards, so I thought I'd ask

I have cell that changes colour (of the column) if you type a certain word, the word match a cell
ie
A1 (car service) = B1 Car Service, then B1 turns red,

is there a way that say instead of car service in B1 "car s" was typed. (note the change in captials) and it would still change colour?

If it can't be done in the macro what about conditional formatting?

below is the code if that helps.

thanks

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, c As Range
Dim ccolor As Range
If Target.Row <> 10 Then Exit Sub
If Target.Column < 12 Or Target.Column > 214 Then Exit Sub

Set rng = Range("L10:HF10")

For Each c In rng
Set ccolor = ActiveSheet.Range(Cells(c.Row, c.Column), Cells(125, c.Column))

If c = [X5] Then ccolor.Interior.ColorIndex = 2
If c = [X6] Then ccolor.Interior.ColorIndex = 37
'If c = [K7] Then ccolor.Interior.ColorIndex = 40
If c = [X7] Then ccolor.Interior.ColorIndex = 36
If c = [X8] Then ccolor.Interior.ColorIndex = 35
If c = "" Then ccolor.Interior.ColorIndex = xlNone
Next
End Sub

 

[Stormseed]

I dunno but would you want something which will change color of the cell depending on the text what you type in a particular cell ?

I have an effective solution

 

[barry houdini]

With conditional formatting, if you want B1 to change colour if it contains "car" (possibly with other text) you can use "formula is" option with formula

=SEARCH("car",B1)

this is not case-sensitive