Problem with Conditional Formatting VBA if cell contains [SOLVED]

[bnpdkh]

I am using the code below to format cells that contain "HWD" or "PH". This is working fine as long as that is the only text contained in the cell, is there a way to modify this code to still format the cell even when other text may be in the same cell. For example; a cell in the identified range may contain RAS, PMT, HWD, the code below will not format this cell.

 Range("I9:I73,I77:I141,I145:I174,I178:I207,I211:I240,I244:I263,I267:I286").Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""HWD"""
    Selection.FormatConditions(1).Interior.ColorIndex = 3
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=""PH"""
    Selection.FormatConditions(2).Interior.ColorIndex = 3

Hope the code is posted correctly! Thanks in advance for any help on this issue.

 

[Luke M]

Try this:

With Range("I9:I73,I77:I141,I145:I174,I178:I207,I211:I240,I244:I263,I267:I286")
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=ISNUMBER(SEARCH(""HWD"",I9))"
    .FormatConditions(1).Interior.ColorIndex = 3
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=ISNUMBER(SEARCH(""PH"",I9))"
    .FormatConditions(2).Interior.ColorIndex = 3
end with

 

[Luke M]

Or, since it looks like they both have the same format condition, you could combine them:

With Range("I9:I73,I77:I141,I145:I174,I178:I207,I211:I240,I244:I263,I267:I286")
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="=OR(ISNUMBER(SEARCH(""HWD"",I9)),ISNUMBER(SEARCH(""PH"",I9)))"
    .FormatConditions(1).Interior.ColorIndex = 3
End With

 

[bnpdkh]

Thanks for the quick response, tried the first suggestion but it did not work, even the cells containing only the HWD or PH text failed to format with this formula. In case you have not noticed, I am at step one in "becoming awsome in Excel" so could you explain why I9 is required in formula?

 

[NARAYANK991]

Hi ,

Try this :

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=ISNUMBER(SEARCH(""HWD"",$I9))"

Narayan

 

[Luke M]

Ah, Narayan hit it. Need to put an absolute reference on the I9, so that it stays looking at column I.

Because we had to bring in the SEARCH function to test if the value was within the cell, we have the follow the rules for that function. SEARCH takes at least 2 arguments, what you want to find, and what you want to search. So, we specify the cell. As you are applying the format to multiple cells, but all within the same column, we make the column an absolute reference, but leave the row a relative reference.

 

[bnpdkh]

That did the trick, thanks to both of you for your help (and explanation) with this issue. Off to work on ActiveX dropdown solution you suggested Luke. Step one underway!!
[SOLVED]