how to select a Row based on contents of a Cell

Hello all... first-time poster here, with what I hope is a simple issue to
bounce off the group. I can't find information about this anywhere. It
isn't specifically addressed in any of the VBA resources I know of....

I believe this is similar to conditional formatting, but I'm trying to work
with entire rows, rather than just cells. Here's what I'm trying to do,
using VBA...

"If any cell in Column X contains value=Y, then perform Z on the entire
Row."

Examples:

If any cell in Column G contains the word "Apple", then the entire Row
containing that cell shuld be Red.

or

If any cell in Column D contains the word "Ohio", then delete the entire
Row.

Can expression be written in VBA? It seems like conditional formatting, but
it's not a "per cell" condition. Any suggestions will be greatly
appreciated.

- Scott

Hi Scott,

Something along these lines ...

Sub Test()

Dim WatchRange As Range
Dim CellTest As Range

Set WatchRange = Range("D1:D100")

For Each CellTest In WatchRange.Cells
If CellTest.Value = "Ohio" Then
ActiveRow.Delete
ActiveCell.Offset(0, -1).Range("A1").Select
End If
Next CellTest
End Sub

HTH
Cheers
Carim

a couple of ways can be used --

Using CF:
-Select rows 1-100 with A1 as the active cell
-Click on Format>CF
-Change the Value box to Formula is
- with your cursor in the equals box select cell $G$1
- click on the F4 (function) key to change the relative property to $G1
-add to this making it =$G1="Apple"
-Click on the Format button and the Pattern Tab and select Red
Click OK a couple of times to exit CF
Any row in row 1 to 100 with "Apple" in col G should now be red .

This will not work for Deleting a row. You must do this either manally or
with vba code, something like the following. Tip: start from the bottom of
your range and work your way up the column of interest looking for the word
or condition. If/when the condition is found then delete the entire row:

Option Explicit

Sub DeleteOhio()
Dim oCell as Range
dim i as integer

For i=100 to 2 Step -1
oCell = Cells(i,7) ' Col G = 7th col
if oCell.value = "Ohio" then
oCell.EntireRow.Delete
end if
Next i

End Sub


This will delete any rows (2-100) that has "Ohio" in col G.

The same vba approach can be used to color a row:
oCell.EntireRow.interior.colorindex=3


"swintronix" wrote:

> Hello all... first-time poster here, with what I hope is a simple issue to
> bounce off the group. I can't find information about this anywhere. It
> isn't specifically addressed in any of the VBA resources I know of....
>
> I believe this is similar to conditional formatting, but I'm trying to work
> with entire rows, rather than just cells. Here's what I'm trying to do,
> using VBA...
>
> "If any cell in Column X contains value=Y, then perform Z on the entire
> Row."
>
> Examples:
>
> If any cell in Column G contains the word "Apple", then the entire Row
> containing that cell shuld be Red.
>
> or
>
> If any cell in Column D contains the word "Ohio", then delete the entire
> Row.
>
> Can expression be written in VBA? It seems like conditional formatting, but
> it's not a "per cell" condition. Any suggestions will be greatly
> appreciated.
>
> - Scott
>
>
>
>

scott -

conditional formatting is meant to only work on the cell that is being
tested - for example if you have conditional format for "A1" you can state
"If A1>100 then make the text blue". You can get it to test for values in
other cells on a limited basis (at least to my knowledge) -- for example

in cell A3 set conditional formatting to: FORMULA IS =($C$3=2)*A3

this basicly uses logic to see if A3 = A3 -- and will only evaluate to true
if $C$3 = 2. so you can trick excel to conditionally format based on values
in other cells. HOWEVER, this is limited as (1) XL only supports 3
conditional formats per cell and (2) it ONLY formats - you cannot use
conditional formatting to perform actions on cells (like DELETE as in your
example)

You could approach your problem several ways. I think the the best solution
is probably to loop through all your tests with an autofilter and perform
the actions within the loop for example:

(the syntax isn't right, just meaning to give you the idea)

set your parameters arrays:

testvalue = array("orange", "CA")
column = (1,3)
action = array("color","delete")
attributes = array("red","na") '''' na used a a placeholder for the 'delete'
action to keep arrays in synch

for i = 1 to 2' that is 2 tests

range.autofilter (where column(i) meets testvalue(i))
for each r in range.specialcells (xlvisible).entirerow
select case action(i)
case "color"
color the visible rows accordiong to the attrributes array
case "delete"
delete the visible rows
end select
next r
next i

this would in effect first color any row where the value in column 1 is
'orange'
then it would delete any row where the value in column 3 is 'CA'

this strategy allows you to have as many actions and tests as you want - and
to change each item at will - w/o having to rewrite the whole script


if you delete, work from the bottom up - because as you delete the rows
below the one you deleted change in number
if you recolor, remember to first set EVERYTHING back to a neutral color or
else you keep the colors you had from the previous macro run


"swintronix" <susancooper@nospamsbcglobal.net> wrote in message
news:j4Juf.40765$dO2.31350@newssvr29.news.prodigy.net...
> Hello all... first-time poster here, with what I hope is a simple issue to
> bounce off the group. I can't find information about this anywhere. It
> isn't specifically addressed in any of the VBA resources I know of....
>
> I believe this is similar to conditional formatting, but I'm trying to
> work
> with entire rows, rather than just cells. Here's what I'm trying to do,
> using VBA...
>
> "If any cell in Column X contains value=Y, then perform Z on the entire
> Row."
>
> Examples:
>
> If any cell in Column G contains the word "Apple", then the entire Row
> containing that cell shuld be Red.
>
> or
>
> If any cell in Column D contains the word "Ohio", then delete the entire
> Row.
>
> Can expression be written in VBA? It seems like conditional formatting,
> but
> it's not a "per cell" condition. Any suggestions will be greatly
> appreciated.
>
> - Scott
>
>
>

Thank you everyone for the ideas! I'm going to try all of them out and let
you know what works for me. Will get back to the group ASAP.

- Scott

"swintronix" <susancooper@nospamsbcglobal.net> wrote in message
news:j4Juf.40765$dO2.31350@newssvr29.news.prodigy.net...
> Hello all... first-time poster here, with what I hope is a simple issue to
> bounce off the group. I can't find information about this anywhere. It
> isn't specifically addressed in any of the VBA resources I know of....
>
> I believe this is similar to conditional formatting, but I'm trying to
work
> with entire rows, rather than just cells. Here's what I'm trying to do,
> using VBA...
>
> "If any cell in Column X contains value=Y, then perform Z on the entire
> Row."
>
> Examples:
>
> If any cell in Column G contains the word "Apple", then the entire Row
> containing that cell shuld be Red.
>
> or
>
> If any cell in Column D contains the word "Ohio", then delete the entire
> Row.
>
> Can expression be written in VBA? It seems like conditional formatting,
but
> it's not a "per cell" condition. Any suggestions will be greatly
> appreciated.
>
> - Scott
>
>
>

"cush" <cush@discussions.microsoft.com> wrote in message
news:27B1C8E2-C54C-45EB-A5A7-3DD496E2435C@microsoft.com...
> a couple of ways can be used --
>
> Using CF:
> -Select rows 1-100 with A1 as the active cell
> -Click on Format>CF
> -Change the Value box to Formula is
> - with your cursor in the equals box select cell $G$1
> - click on the F4 (function) key to change the relative property to $G1
> -add to this making it =$G1="Apple"
> -Click on the Format button and the Pattern Tab and select Red
> Click OK a couple of times to exit CF
> Any row in row 1 to 100 with "Apple" in col G should now be red .
>

Cush,

This worked like a charm. I performed this action, and now have this
recorded, cleaned up macro:

Cells.Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=$H1=""JOHN SMITH"""
Selection.FormatConditions(1).Interior.ColorIndex = 3

It finds everything in Column H containing the phrase "JOHN SMITH" and
colors the entire Row with background of RED.

Question: What is the difference between $H$1 and $H1? I think that
understanding this is the key to my issue of Cell vs. Entire Row, but I'm
unclear on what the syntax means.

thanks!

This is great stuff. thanks Joe!

"voodooJoe" <NA@NA.NET> wrote in message
news:4p-dnfKSN7SR9SbenZ2dnUVZ_tmdnZ2d@comcast.com...
> scott -
>
> conditional formatting is meant to only work on the cell that is being
> tested - for example if you have conditional format for "A1" you can state
> "If A1>100 then make the text blue". You can get it to test for values in
> other cells on a limited basis (at least to my knowledge) -- for example
>
> in cell A3 set conditional formatting to: FORMULA IS =($C$3=2)*A3
>
> this basicly uses logic to see if A3 = A3 -- and will only evaluate to
true
> if $C$3 = 2. so you can trick excel to conditionally format based on
values
> in other cells. HOWEVER, this is limited as (1) XL only supports 3
> conditional formats per cell and (2) it ONLY formats - you cannot use
> conditional formatting to perform actions on cells (like DELETE as in your
> example)
>
> You could approach your problem several ways. I think the the best
solution
> is probably to loop through all your tests with an autofilter and perform
> the actions within the loop for example:
>
> (the syntax isn't right, just meaning to give you the idea)
>
> set your parameters arrays:
>
> testvalue = array("orange", "CA")
> column = (1,3)
> action = array("color","delete")
> attributes = array("red","na") '''' na used a a placeholder for the
'delete'
> action to keep arrays in synch
>
> for i = 1 to 2' that is 2 tests
>
> range.autofilter (where column(i) meets testvalue(i))
> for each r in range.specialcells (xlvisible).entirerow
> select case action(i)
> case "color"
> color the visible rows accordiong to the attrributes array
> case "delete"
> delete the visible rows
> end select
> next r
> next i
>
> this would in effect first color any row where the value in column 1 is
> 'orange'
> then it would delete any row where the value in column 3 is 'CA'
>
> this strategy allows you to have as many actions and tests as you want -
and
> to change each item at will - w/o having to rewrite the whole script
>
>
> if you delete, work from the bottom up - because as you delete the rows
> below the one you deleted change in number
> if you recolor, remember to first set EVERYTHING back to a neutral color
or
> else you keep the colors you had from the previous macro run
>
>
> "swintronix" <susancooper@nospamsbcglobal.net> wrote in message
> news:j4Juf.40765$dO2.31350@newssvr29.news.prodigy.net...
> > Hello all... first-time poster here, with what I hope is a simple issue
to
> > bounce off the group. I can't find information about this anywhere. It
> > isn't specifically addressed in any of the VBA resources I know of....
> >
> > I believe this is similar to conditional formatting, but I'm trying to
> > work
> > with entire rows, rather than just cells. Here's what I'm trying to do,
> > using VBA...
> >
> > "If any cell in Column X contains value=Y, then perform Z on the entire
> > Row."
> >
> > Examples:
> >
> > If any cell in Column G contains the word "Apple", then the entire Row
> > containing that cell shuld be Red.
> >
> > or
> >
> > If any cell in Column D contains the word "Ohio", then delete the entire
> > Row.
> >
> > Can expression be written in VBA? It seems like conditional formatting,
> > but
> > it's not a "per cell" condition. Any suggestions will be greatly
> > appreciated.
> >
> > - Scott
> >
> >
> >
>
>

This gets me started. Thank you so much.

"Carim" <carim.fam@wanadoo.fr> wrote in message
news:1136353789.366467.228720@z14g2000cwz.googlegroups.com...
> Hi Scott,
>
> Something along these lines ...
>
> Sub Test()
>
> Dim WatchRange As Range
> Dim CellTest As Range
>
> Set WatchRange = Range("D1:D100")
>
> For Each CellTest In WatchRange.Cells
> If CellTest.Value = "Ohio" Then
> ActiveRow.Delete
> ActiveCell.Offset(0, -1).Range("A1").Select
> End If
> Next CellTest
> End Sub
>
> HTH
> Cheers
> Carim
>

I was also struggling to come up with the VBA code to highlight cells a
certain color. The macro you created worked great! I do have one question.
How would you modify the macro to look for multiple values? For example, I
am looking for John Smith and Jane Doe. I have been playing with this for a
while and I cannot get this macro to accept more than one value. Any ideas?