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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,362
Office Version
365
Platform
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
75,362
Office Version
365
Platform
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
75,362
Office Version
365
Platform
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.
 

Forum statistics

Threads
1,081,747
Messages
5,361,038
Members
400,610
Latest member
ebey

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top