Conditional Formatting for everything BUT one word

[rhowse8]

All,

Is there a way to color cells based on a value that is not contained? For my application any cell that doesn't contain the word "complete" would be yellow and every cell that does contain the word "complete" would be green. I know how to format the cells to turn green when complete is written, however, I am having trouble finding a way to format the other cells.

Anyone know how to solve this? I have tried using conditional formatting and can't see to get it right.

Thanks,

rhowse8

 

[Eric W]

If you know how to set up Conditional Formatting for the green cells, then do the same thing for the yellow cells using this function:

=ISERROR(SEARCH("complete",F1))

where F1 is the upper left corner of your selection.

 

[ask2tsp]

first format background color yellow in all cells with data in that column
next apply conditional formatting to that same range of cells (if "complete" color green.

In code (assuming data starts in cel A1)
paste it in the codepage of the sheet where the formatting takes place.

Option Explicit

Sub colorStatus()
   Dim statRng As Range
   
   Set statRng = Me.Range("A1", "A" & Me.Cells(Rows.Count, 1).End(xlUp).Row)
   statRng.Interior.Color = RGB(255, 255, 0)
   
   statRng.FormatConditions.Add Type:=xlTextString, String:="complete", _
        TextOperator:=xlContains
   
   statRng.FormatConditions(statRng.FormatConditions.Count).SetFirstPriority
   
   With statRng.FormatConditions(1).Interior
      .PatternColorIndex = xlAutomatic
      .Color = 5296274
      .TintAndShade = 0
   End With

End Sub