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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
Office Version
  1. 365
Platform
  1. Windows
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.
 

bjw122

New Member
Joined
Oct 3, 2005
Messages
16
Can someone suggest a code solution to me (or fix mine in the original post). Norie suggests the InStr function.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
Office Version
  1. 365
Platform
  1. Windows
Did you try the conditional formatting?

If you did, did it work?
 

bjw122

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,996
Messages
5,834,784
Members
430,322
Latest member
excelnoobnoob

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
Top