Loop range change a cell color

[CR]

I have a Range A10:Q85
In each row, starting in column B, some of the cells may have a text value
that ends in -done , such as modelA-done or Chev-done.

I would like to loop each row and if any cell has that -done value, change
the cell in column A of that row to Font.color = 4

Thanks
CR

 

[B Lynn B]

sub done4()

Dim myRow as long
Application.ScreenUpdating = False

For Each cell in Range("B10:Q85").cells
If Right(cell.value, 5) = "-done" then
Cells(cell.row, 1).Font.ColorIndex = 4
Next

End Sub

Just a couple of suggestions - give the range a name instead of referring to
its address. That way if you add rows, you won't have to go in and edit your
code to keep it working. Also be sure the string "-done" would always be an
exact match - no upper case characters, and no spaces or other punctuation
following. Otherwise you might want to use some combination of UCase and
Instr function as well to ensure all matches are found.

 

[B Lynn B]

Oops, no need to Dim myRow - I was going to use it to hold the row number but
then decided the extra step wasn't necessary.

 

[Dave Peterson]

Do you have to use a macro?

You could use format|conditional formatting (xl2003 menus) to accomplish the
same thing:

Select A10:A85
Format|conditional formatting
Formula is:
=countif(b10:q10,"*-done")>0
(excel will adjust the row numbers for each row in the selection (10:85))

And give it a nice format.

=====
Ps. I wouldn't use this. Colors are nice, but you still have to eyeball it to
find what you're looking for.

I'd insert a new column B--now your range to check is in C:R.

And put this in B10:
=countif(c10:r10,"*-done")
and apply data|filter|autofilter to show the values greater than 0.

If you only wanted to see true or false, you could use:
=countif(c10:r10,"*-done")>0

=====
Actually, I'd use both the conditional formatting and the additional column--but
I'd base the conditional formatting on that helper column (so I didn't need to
modify the formula in both spots when(!) it changed).

 

[CR]

Thank you both.
I tried to use conditional formatting but couldn't come up with a formula
without the very unhelpful error dialog box. (The usual result of me trying
to come up with a formula)

I should be able accomplish the task now.

CR