macro formula/conditional formatting

[jmkerzic]

here is my macro. I have to highlight rows that only have the word "pad" in column M only. I changed it to M:M but it highlighted several columns that did not have the word pad in it.

Selection.FormatConditions(1).StopIfTrue = False
Cells.Select
Selection.FormatConditions.add Type:=xlExpression, Formula1:= _
"=COUNTIF(1:1,""*pad*"")"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 9419919
.TintAndShade = 0
End With

 

[DanteAmor]

Try this

Sub Macro1()
  Dim rng As Range
  Set rng = Cells
  Range("A1").Select
  Cells.FormatConditions.Delete
  rng.FormatConditions.Add Type:=xlExpression, Formula1:="=[COLOR=#333333]COUNTIF[/COLOR]($M1,""*pad*"")"
  rng.FormatConditions(rng.FormatConditions.Count).SetFirstPriority
  With rng.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 9419919
    .TintAndShade = 0
  End With
  rng.FormatConditions(1).StopIfTrue = False
End Sub

 

[Peter_SSs]

I have to highlight rows that only have the word "pad" in column M only.

My interpretation of that is that column M must contain "pad" and no other columns are allowed to contain "pad".

Also. I would recommend against ..
- applying Conditional Formatting to the whole worksheet
- using a COUNTIF function to count a whole row or whole column.

Try to limit the ranges if you can. So if my interpretation above is correct my suggestion would be something like

Sub CheckPad()
  With Range("A1:BZ1000")
    .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(SEARCH(""pad"",$M1),COUNTIF($A1:$BZ1,""*pad*"")=1)"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Interior
      .PatternColorIndex = xlAutomatic
      .Color = 9419919
      .TintAndShade = 0
    End With
  End With
End Sub