Changing row colour if text string present in a cell?

S

StargateFan

I'm hoping that something like this can be done: if any email address
in a worksheet has a certain text string in it, in this case
"@agr.gc.ca", what would the conditional formatting code be that will
then colour that row yellow, if this is indeed possible? The rows go
from 2 to 15, if that info is needed, i.e., A2 to A15, B2 to B15, etc.
(or if necessary, if it's easier to just code the entire row, that
would be acceptable, too, rather than the area of row that is visible
in the sheet).

What this would do is to signal records of people that are inside the
organization vs other govt departments.

Thanks much. Appreciate the help.
 
G

Guest

Maybe you can use this:

With Range(x)
.FormatConditions.Add Type:=xlCellValue,
Operator:=xlEqual, Formula1:="=""@agr.gc.ca"""
.FormatConditions(1).Interior.Color = vbYellow
End With
But this will only color the given cell

To color the entire row adding this should work (maybe
put it under the Worksheet.Change event):

If Range(x).Interior.Color=vbYellow then
rows(Range(x).Row).Interior.Color=vbYellow
End If
 
I

Iain King

StargateFan said:
I'm hoping that something like this can be done: if any email address
in a worksheet has a certain text string in it, in this case
"@agr.gc.ca", what would the conditional formatting code be that will
then colour that row yellow, if this is indeed possible? The rows go
from 2 to 15, if that info is needed, i.e., A2 to A15, B2 to B15, etc.
(or if necessary, if it's easier to just code the entire row, that
would be acceptable, too, rather than the area of row that is visible
in the sheet).
Click to expand...

is there an email in only one column per row? if so then you just have to
select the cells in row 1, go to conditional formating . change the
condition to Formula=, and enter (assuming the email appears in column A)

=search("@agr.gc.ca",$A1)

and set the formating to yellow background

then select the cells, copy. select all the cells below that row and paste
special 'formats'

If the emails appear more than once per row then it's a little more
complicated, so post back in that case.

Iain King
 
T

Tom Ogilvy

If the color is set with Conditional formatting, then your suggestion of
If Range(x).Interior.Color=vbYellow then
rows(Range(x).Row).Interior.Color=vbYellow
End If
Click to expand...

will not work as you can not query a cell for a color being produced by
conditional formatting.
 
Top