Conditionally Change Fill Color of Row

bjw122

New Member
Joined
Oct 3, 2005
Messages
16
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.

Code:
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
bjw122 said:
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.
 
Upvote 0
Can someone suggest a code solution to me (or fix mine in the original post). Norie suggests the InStr function.
 
Upvote 0
Did you try the conditional formatting?

If you did, did it work?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,211,686
Messages
6,103,298
Members
447,853
Latest member
olddutch7

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top