Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hi-light area based on name found in column A
How would I highlight an area in a spreadsheet based on a name found in
column A? such as ... if the name frog was found in column A .. how would I highlight through a macro ... col C, D, and E all the rows that have Frog listed in Column A? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hi-light area based on name found in column A
Use conditional formatting.
Set up your formula to look at the entry in column A Select column C,D,E with C1 as the active cell Under format=Conditional formatting change Cell Value is to Formula is using the dropdown. In the text box put in the formula =$A1="frog" then click format and choose the formatting you want. -- Regards, Tom Ogilvy "Annette" wrote in message ... How would I highlight an area in a spreadsheet based on a name found in column A? such as ... if the name frog was found in column A .. how would I highlight through a macro ... col C, D, and E all the rows that have Frog listed in Column A? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hi-light area based on name found in column A
If I understand your question correctly then…
lr = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count For x = 1 To lr If Worksheets("Sheet1").Cells(x, 1).Value = "Frog" Then Worksheets("Sheet1").Rows(x).Interior.ColorIndex = 6 Worksheets("Sheet1").Columns("C:E").Interior.Color Index = 6 End If Next x - Piku -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hi-light area based on name found in column A
But how would I write this into a macro or write the macro for this?
"Tom Ogilvy" wrote in message ... Use conditional formatting. Set up your formula to look at the entry in column A Select column C,D,E with C1 as the active cell Under format=Conditional formatting change Cell Value is to Formula is using the dropdown. In the text box put in the formula =$A1="frog" then click format and choose the formatting you want. -- Regards, Tom Ogilvy "Annette" wrote in message ... How would I highlight an area in a spreadsheet based on a name found in column A? such as ... if the name frog was found in column A .. how would I highlight through a macro ... col C, D, and E all the rows that have Frog listed in Column A? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hi-light area based on name found in column A
Turn on the macro recorder while you do it manually.
-- Regards, Tom Ogilvy "Annette" wrote in message ... But how would I write this into a macro or write the macro for this? "Tom Ogilvy" wrote in message ... Use conditional formatting. Set up your formula to look at the entry in column A Select column C,D,E with C1 as the active cell Under format=Conditional formatting change Cell Value is to Formula is using the dropdown. In the text box put in the formula =$A1="frog" then click format and choose the formatting you want. -- Regards, Tom Ogilvy "Annette" wrote in message ... How would I highlight an area in a spreadsheet based on a name found in column A? such as ... if the name frog was found in column A .. how would I highlight through a macro ... col C, D, and E all the rows that have Frog listed in Column A? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hi-light area based on name found in column A
If you are going to color the whole row, why repeat the same command on only
columns C:E? Worksheets("Sheet1").Rows(x).Interior.ColorInde x = 6 Worksheets("Sheet1").Columns("C:E").Interior.Colo rIndex = 6 While probably not a big deal, you correctly calculate the last row of the usedrange using a formula that recognizes it doesn't necessarily start in row 1, then use a hard coded 1 as the start row for your loop. lr = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count ? lr 21 ? Activesheet.UsedRange.rows.count 11 In this case, the usedRange didn't start with row 1. -- Regards, Tom Ogilvy "pikus " wrote in message ... If I understand your question correctly then. lr = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count For x = 1 To lr If Worksheets("Sheet1").Cells(x, 1).Value = "Frog" Then Worksheets("Sheet1").Rows(x).Interior.ColorIndex = 6 Worksheets("Sheet1").Columns("C:E").Interior.Color Index = 6 End If Next x - Pikus --- Message posted from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hi-light area based on name found in column A
Getting closer ... I modified this a bit to fit my needs, but now ... here's
the problem, I don't want to select the entire columns, rather ... just those rows that match the Frog: Here's what I did to modify: Sub hilit() lr = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count For x = 1 To lr If Worksheets("Sheet2").Cells(x, 1).Value = "Frog" Then Worksheets("Sheet2").Rows(x).Select Worksheets("Sheet2").Columns("C:E").Select End If Next x End Sub "pikus " wrote in message ... If I understand your question correctly then. lr = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count For x = 1 To lr If Worksheets("Sheet1").Cells(x, 1).Value = "Frog" Then Worksheets("Sheet1").Rows(x).Interior.ColorIndex = 6 Worksheets("Sheet1").Columns("C:E").Interior.Color Index = 6 End If Next x - Pikus --- Message posted from http://www.ExcelForum.com/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hi-light area based on name found in column A
I don't want to select the entire columns
so why right code that does that: Worksheets("Sheet2").Columns("C:E").Select why not worksheets("Sheet2").Cells(x,3).Resize(1,3).Interi or.ColorIndex = 6 rather than Worksheets("Sheet2").Rows(x).Select Worksheets("Sheet2").Columns("C:E").Select -- Regards, Tom Ogilvy "Annette" wrote in message ... Getting closer ... I modified this a bit to fit my needs, but now ... here's the problem, I don't want to select the entire columns, rather ... just those rows that match the Frog: Here's what I did to modify: Sub hilit() lr = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count For x = 1 To lr If Worksheets("Sheet2").Cells(x, 1).Value = "Frog" Then Worksheets("Sheet2").Rows(x).Select Worksheets("Sheet2").Columns("C:E").Select End If Next x End Sub "pikus " wrote in message ... If I understand your question correctly then. lr = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count For x = 1 To lr If Worksheets("Sheet1").Cells(x, 1).Value = "Frog" Then Worksheets("Sheet1").Rows(x).Interior.ColorIndex = 6 Worksheets("Sheet1").Columns("C:E").Interior.Color Index = 6 End If Next x - Pikus --- Message posted from http://www.ExcelForum.com/ |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hi-light area based on name found in column A
Okay, I'm not understanding this so let me just explain one more time. I
want to find every row in column A that contains the word "Frog" ... and select every row in col D and E that correspond to this. col a d e toad 1 2 toad 1 5 frog 1 5 frog 3 7 frog 2 5 So my result will be the row 3, 4, and 5 with col D and E of those row selected. The macro is only selecting the last row. Thanks! "Tom Ogilvy" wrote in message ... I don't want to select the entire columns so why right code that does that: Worksheets("Sheet2").Columns("C:E").Select why not worksheets("Sheet2").Cells(x,3).Resize(1,3).Interi or.ColorIndex = 6 rather than Worksheets("Sheet2").Rows(x).Select Worksheets("Sheet2").Columns("C:E").Select -- Regards, Tom Ogilvy "Annette" wrote in message ... Getting closer ... I modified this a bit to fit my needs, but now ... here's the problem, I don't want to select the entire columns, rather ... just those rows that match the Frog: Here's what I did to modify: Sub hilit() lr = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count For x = 1 To lr If Worksheets("Sheet2").Cells(x, 1).Value = "Frog" Then Worksheets("Sheet2").Rows(x).Select Worksheets("Sheet2").Columns("C:E").Select End If Next x End Sub "pikus " wrote in message ... If I understand your question correctly then. lr = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count For x = 1 To lr If Worksheets("Sheet1").Cells(x, 1).Value = "Frog" Then Worksheets("Sheet1").Rows(x).Interior.ColorIndex = 6 Worksheets("Sheet1").Columns("C:E").Interior.Color Index = 6 End If Next x - Pikus --- Message posted from http://www.ExcelForum.com/ |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hi-light area based on name found in column A
I guess Hilight was interpreted as coloring the cell.
originally you said C to E Sub hilit() Dim rng as Range, x as long, lr as long lr = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count For x = 1 To lr If Worksheets("Sheet2").Cells(x, 1).Value = "Frog" Then if rng is nothing then set rng = Worsheets("sheet2").Cells(x,4).Resize(1,2) else set rng = Union(rng,Worksheets("Sheet2").Cells(x,4).Resize(1 ,2)) End if End If Next x if not rng is nothing then rng.Select End if End Sub -- Regards, Tom Ogilvy "Annette" wrote in message ... Okay, I'm not understanding this so let me just explain one more time. I want to find every row in column A that contains the word "Frog" ... and select every row in col D and E that correspond to this. col a d e toad 1 2 toad 1 5 frog 1 5 frog 3 7 frog 2 5 So my result will be the row 3, 4, and 5 with col D and E of those row selected. The macro is only selecting the last row. Thanks! "Tom Ogilvy" wrote in message ... I don't want to select the entire columns so why right code that does that: Worksheets("Sheet2").Columns("C:E").Select why not worksheets("Sheet2").Cells(x,3).Resize(1,3).Interi or.ColorIndex = 6 rather than Worksheets("Sheet2").Rows(x).Select Worksheets("Sheet2").Columns("C:E").Select -- Regards, Tom Ogilvy "Annette" wrote in message ... Getting closer ... I modified this a bit to fit my needs, but now ... here's the problem, I don't want to select the entire columns, rather ... just those rows that match the Frog: Here's what I did to modify: Sub hilit() lr = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count For x = 1 To lr If Worksheets("Sheet2").Cells(x, 1).Value = "Frog" Then Worksheets("Sheet2").Rows(x).Select Worksheets("Sheet2").Columns("C:E").Select End If Next x End Sub "pikus " wrote in message ... If I understand your question correctly then. lr = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count For x = 1 To lr If Worksheets("Sheet1").Cells(x, 1).Value = "Frog" Then Worksheets("Sheet1").Rows(x).Interior.ColorIndex = 6 Worksheets("Sheet1").Columns("C:E").Interior.Color Index = 6 End If Next x - Pikus --- Message posted from http://www.ExcelForum.com/ |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hi-light area based on name found in column A
Yes .. this is it ... thank you thank you thank ... I really try to write
the correct terminology but I failed miserably here. Once you understood what I needed, you provided the perfect answer. Thank you! "Tom Ogilvy" wrote in message ... I guess Hilight was interpreted as coloring the cell. originally you said C to E Sub hilit() Dim rng as Range, x as long, lr as long lr = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count For x = 1 To lr If Worksheets("Sheet2").Cells(x, 1).Value = "Frog" Then if rng is nothing then set rng = Worsheets("sheet2").Cells(x,4).Resize(1,2) else set rng = Union(rng,Worksheets("Sheet2").Cells(x,4).Resize(1 ,2)) End if End If Next x if not rng is nothing then rng.Select End if End Sub -- Regards, Tom Ogilvy "Annette" wrote in message ... Okay, I'm not understanding this so let me just explain one more time. I want to find every row in column A that contains the word "Frog" ... and select every row in col D and E that correspond to this. col a d e toad 1 2 toad 1 5 frog 1 5 frog 3 7 frog 2 5 So my result will be the row 3, 4, and 5 with col D and E of those row selected. The macro is only selecting the last row. Thanks! "Tom Ogilvy" wrote in message ... I don't want to select the entire columns so why right code that does that: Worksheets("Sheet2").Columns("C:E").Select why not worksheets("Sheet2").Cells(x,3).Resize(1,3).Interi or.ColorIndex = 6 rather than Worksheets("Sheet2").Rows(x).Select Worksheets("Sheet2").Columns("C:E").Select -- Regards, Tom Ogilvy "Annette" wrote in message ... Getting closer ... I modified this a bit to fit my needs, but now .... here's the problem, I don't want to select the entire columns, rather ... just those rows that match the Frog: Here's what I did to modify: Sub hilit() lr = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count For x = 1 To lr If Worksheets("Sheet2").Cells(x, 1).Value = "Frog" Then Worksheets("Sheet2").Rows(x).Select Worksheets("Sheet2").Columns("C:E").Select End If Next x End Sub "pikus " wrote in message ... If I understand your question correctly then. lr = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count For x = 1 To lr If Worksheets("Sheet1").Cells(x, 1).Value = "Frog" Then Worksheets("Sheet1").Rows(x).Interior.ColorIndex = 6 Worksheets("Sheet1").Columns("C:E").Interior.Color Index = 6 End If Next x - Pikus --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 noncontiguous cell highlight color=light,light blue. How do I make it darker, or like Excel 2003 ( I'm extremely shade blind-PLZ HELP !! ) | New Users to Excel | |||
Excel 2007 noncontiguous cell highlight color=light,light blue. How do I make it darker, or like Excel 2003 ( I'm extremely shade blind-PLZ HELP !! ) | Excel Discussion (Misc queries) | |||
traffic light based on 3 different conditions | Excel Worksheet Functions | |||
Subtotals of Items in A Column based on Spec. Values found in Colu | Excel Discussion (Misc queries) | |||
How to replace the background color of all cells from light green to light blue? | Excel Discussion (Misc queries) |