Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to select a Row based on contents of a Cell
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to select a Row based on contents of a Cell
a couple of ways can be used --
Using CF: -Select rows 1-100 with A1 as the active cell -Click on FormatCF -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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to select a Row based on contents of a Cell
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 A1100 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" wrote in message . 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to select a Row based on contents of a Cell
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" wrote in message . 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to select a Row based on contents of a Cell
"cush" wrote in message ... a couple of ways can be used -- Using CF: -Select rows 1-100 with A1 as the active cell -Click on FormatCF -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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to select a Row based on contents of a Cell
This is great stuff. thanks Joe!
"voodooJoe" wrote in message ... 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 A1100 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" wrote in message . 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to select a Row based on contents of a Cell
This gets me started. Thank you so much.
"Carim" wrote in message oups.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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to select a Row based on contents of a Cell
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I select & use contents of first non-blank cell in row? | Excel Worksheet Functions | |||
select cell at random and delete contents | Excel Programming | |||
select contents of cell | Excel Programming | |||
select a cell based on A1 contents | Excel Programming | |||
Can I select a range based on the cell's contents? | Excel Programming |