Conditionally Change Fill Color of Row

[bjw122]

I could use some help with this routine. The intent is this: If a cell in column 'E' conains the substring "See CPAAI team" then change the fill color for the row from columns A through E. I don't want to use conditional highlighting because the search string can be a substring of the contents of the cell.

Dim LastRow As Long, RowCnt As Long
Dim ArrCnt as Integer, ForCnt As Integer
Dim rng As Range

Application.ScreenUpdating = False

LastRow = Range("A65536").End(xlUp).Row
For RowCnt = 1 to LastRow
  Set rng = Range("E" & RowCnt).MergeArea
  'Sometimes the values in "E" have trailing spaces, remove them
  rng.Value = Trim(rng.Value)
  If rng.Cells(1,1) [code for contains subsring]  "See CPAAI team" Then

    [Code to change fill color to light yellow for the row (from columns A through E) here ]

  End If
Next RowCnt

set rng = Nothing
Application.ScreenUpdating = True
End Sub

 

[Norie]

I. I don't want to use conditional highlighting because the search string can be a substring of the contents of the cell.

Do you mean conditional formatting?

If so then you can easily use it with a formula to do the formatting as required.

Select the required range in columns A to E, goto Format>Conditional Formatting..., select Formula is from the dropdown and try this.

=ISNUMBER(FIND("See CPAAI team", $E1))

If you still want to stick with code take a look at the InStr function.

 

[bjw122]

Can someone suggest a code solution to me (or fix mine in the original post). Norie suggests the InStr function.

 

[Norie]

Did you try the conditional formatting?

If you did, did it work?

 

[bjw122]

Oh sorry I didn't report back.

I did try the conditional formatting but I couldn't get it to apply to each cell correctly. In other words, if I entered
=ISNUMBER(FIND("See CPAAI team", $E3)) it would work for cell E3 but no other cells (or course).

If I left it as you had written and applied the formula to the whole range per your directions:

Select the required range in columns A to E, goto Format>Conditional Formatting..., select Formula is from the dropdown and try this.
=ISNUMBER(FIND("See CPAAI team", $E1))

then none of the formatting took place in the spreadsheet. It's like the formula couldn't propogate along the column right. . .? I then gave up on that approach.

 

[Norie]

Where does your data start is it in row 3?

If it is select the range A3:E(whatever) and try the formula you posted.

Excel should change the cell references as required.

The $ before the E anchors the formula to column E, which I asssume is the column you wish to check for the text. The row should change.

By the way if you can get it to work in E3 you should just be able to use the Format Painter to copy the format to other cells.

Again Excel should change the cell reference as required.