Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and highlight results macro
Is it possible to program Excel's Find dialog so that it will search for all
occurrences of a given string and highlight the rows containing the search string? What I need is a macro (or something) that will prompt me for a string to look for, then go through every cell and highlight the rows that contain the string I specified. Does anyone have a macro for this? If not, can someone please help me create a macro that will do this? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and highlight results macro
Hi,
Sub FindAndColour() Dim c as range Dim Findstr As String Findstr = InputBox("Enter search string") ' Enter your search string With Worksheets(1).Range("a1:D500") ' Change to reflect your search range Set c = .Find(Findstr, LookIn:=xlValues,Lookat:=XlWhole) If Not c Is Nothing Then firstAddress = c.Address Do c.EntireRow.Interior.ColorIndex = 4 ' Set row to green Set c = .FindNext(c) ' Look for next occurence of search string Loop While Not c Is Nothing And c.Address < firstAddress End If End With HTH "Mick" wrote: Is it possible to program Excel's Find dialog so that it will search for all occurrences of a given string and highlight the rows containing the search string? What I need is a macro (or something) that will prompt me for a string to look for, then go through every cell and highlight the rows that contain the string I specified. Does anyone have a macro for this? If not, can someone please help me create a macro that will do this? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and highlight results macro
Just to add --
Most of the code provided can be found in the vba help on the findnext method. In both cases, since you are only marking the found cell and not removing the searched for text, the terminating condition only needs to be: Loop While c.Address < firstAddress rather than Loop While Not c Is Nothing And c.Address < firstAddress c will never be nothing. -- Regards, Tom Ogilvy "Toppers" wrote in message ... Hi, Sub FindAndColour() Dim c as range Dim Findstr As String Findstr = InputBox("Enter search string") ' Enter your search string With Worksheets(1).Range("a1:D500") ' Change to reflect your search range Set c = .Find(Findstr, LookIn:=xlValues,Lookat:=XlWhole) If Not c Is Nothing Then firstAddress = c.Address Do c.EntireRow.Interior.ColorIndex = 4 ' Set row to green Set c = .FindNext(c) ' Look for next occurence of search string Loop While Not c Is Nothing And c.Address < firstAddress End If End With HTH "Mick" wrote: Is it possible to program Excel's Find dialog so that it will search for all occurrences of a given string and highlight the rows containing the search string? What I need is a macro (or something) that will prompt me for a string to look for, then go through every cell and highlight the rows that contain the string I specified. Does anyone have a macro for this? If not, can someone please help me create a macro that will do this? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and highlight results macro
Thanks Toppers!
I tried your macro, but it didn't seem to do anything until I changed 'Loop While' as Tom suggested. I have a couple more question regarding this macro. Please see my next post if you will. "Toppers" wrote: Hi, Sub FindAndColour() Dim c as range Dim Findstr As String Findstr = InputBox("Enter search string") ' Enter your search string With Worksheets(1).Range("a1:D500") ' Change to reflect your search range Set c = .Find(Findstr, LookIn:=xlValues,Lookat:=XlWhole) If Not c Is Nothing Then firstAddress = c.Address Do c.EntireRow.Interior.ColorIndex = 4 ' Set row to green Set c = .FindNext(c) ' Look for next occurence of search string Loop While Not c Is Nothing And c.Address < firstAddress End If End With HTH "Mick" wrote: Is it possible to program Excel's Find dialog so that it will search for all occurrences of a given string and highlight the rows containing the search string? What I need is a macro (or something) that will prompt me for a string to look for, then go through every cell and highlight the rows that contain the string I specified. Does anyone have a macro for this? If not, can someone please help me create a macro that will do this? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and highlight results macro
Thanks Tom!
I tried Toppers macro, but it didn't seem to do anything until I changed the 'Loop While' part as you suggested. I still have a couple of questions for you guys regarding this macro though. Please, check out my next post. "Tom Ogilvy" wrote: Just to add -- Most of the code provided can be found in the vba help on the findnext method. In both cases, since you are only marking the found cell and not removing the searched for text, the terminating condition only needs to be: Loop While c.Address < firstAddress rather than Loop While Not c Is Nothing And c.Address < firstAddress c will never be nothing. -- Regards, Tom Ogilvy "Toppers" wrote in message ... Hi, Sub FindAndColour() Dim c as range Dim Findstr As String Findstr = InputBox("Enter search string") ' Enter your search string With Worksheets(1).Range("a1:D500") ' Change to reflect your search range Set c = .Find(Findstr, LookIn:=xlValues,Lookat:=XlWhole) If Not c Is Nothing Then firstAddress = c.Address Do c.EntireRow.Interior.ColorIndex = 4 ' Set row to green Set c = .FindNext(c) ' Look for next occurence of search string Loop While Not c Is Nothing And c.Address < firstAddress End If End With HTH "Mick" wrote: Is it possible to program Excel's Find dialog so that it will search for all occurrences of a given string and highlight the rows containing the search string? What I need is a macro (or something) that will prompt me for a string to look for, then go through every cell and highlight the rows that contain the string I specified. Does anyone have a macro for this? If not, can someone please help me create a macro that will do this? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and highlight results macro
The code you guys gave me does everything I want... almost. :) You guys are great, and I really appreciate your help, but I have a couple more questions... 1. Would it be possible to... a. Have it use the currently selected fill color for the highlighting? b. Prompt for a color to use for the highlighting? 2. Can I make it search the entire worksheet, without a hard-coded Range? Sub FindAndColour() Dim c as range Dim Findstr As String Findstr = InputBox("Enter search string") ' Enter your search string With Worksheets(1).Range("a1:D500") ' Change to reflect your search range Set c = .Find(Findstr, LookIn:=xlValues,Lookat:=XlWhole) If Not c Is Nothing Then firstAddress = c.Address Do c.EntireRow.Interior.ColorIndex = 4 ' Set row to green Set c = .FindNext(c) ' Look for next occurence of search string Loop While Not c Is Nothing And c.Address < firstAddress End If End With End Sub "Mick" wrote: Is it possible to program Excel's Find dialog so that it will search for all occurrences of a given string and highlight the rows containing the search string? What I need is a macro (or something) that will prompt me for a string to look for, then go through every cell and highlight the rows that contain the string I specified. Does anyone have a macro for this? If not, can someone please help me create a macro that will do this? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and highlight results macro
Tom,
Just FYI - in Excel 2003 both versions of the code work i.e. with/without the "Not C is Nothing". Logically (to me!) it should work even if it is redundant. "Tom Ogilvy" wrote: Just to add -- Most of the code provided can be found in the vba help on the findnext method. In both cases, since you are only marking the found cell and not removing the searched for text, the terminating condition only needs to be: Loop While c.Address < firstAddress rather than Loop While Not c Is Nothing And c.Address < firstAddress c will never be nothing. -- Regards, Tom Ogilvy "Toppers" wrote in message ... Hi, Sub FindAndColour() Dim c as range Dim Findstr As String Findstr = InputBox("Enter search string") ' Enter your search string With Worksheets(1).Range("a1:D500") ' Change to reflect your search range Set c = .Find(Findstr, LookIn:=xlValues,Lookat:=XlWhole) If Not c Is Nothing Then firstAddress = c.Address Do c.EntireRow.Interior.ColorIndex = 4 ' Set row to green Set c = .FindNext(c) ' Look for next occurence of search string Loop While Not c Is Nothing And c.Address < firstAddress End If End With HTH "Mick" wrote: Is it possible to program Excel's Find dialog so that it will search for all occurrences of a given string and highlight the rows containing the search string? What I need is a macro (or something) that will prompt me for a string to look for, then go through every cell and highlight the rows that contain the string I specified. Does anyone have a macro for this? If not, can someone please help me create a macro that will do this? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and highlight results macro
Mick,
I have just added another input box to ask for the colour code (number) and extended the range to whole worksheet using Cells. How do we know what is the currently selected fill colour? If you already have an coloured cell selected PRIOR to running the macro (this is the Activecell), then you could use: c.EntireRow.Interior.ColorIndex = ActiveCell.interior.ColorIndex Equally, you could enter the address of a coloured cell and use: ColAddr = InputBox("Enter Cell Address") ' Enter Cell address e.g A1 Colour = Range(ColAddr).Interior.ColorIndex HTH P.S. Sorry about the earlier code problem but both versions work in Excel 2003 ------------------------------------------------------------------------------------------- Sub FindAndColour() Dim c As Range Dim Findstr As String Dim Colour As Integer Findstr = InputBox("Enter search string") ' Enter your search string Colour = InputBox("Enter Colour code") ' Enter value for colour code With Worksheets(1).Cells ' Whole sheet Set c = .Find(Findstr, LookIn:=xlValues, Lookat:=xlWhole) If Not c Is Nothing Then firstAddress = c.Address Do c.EntireRow.Interior.ColorIndex = Colour ' Set row to green Set c = .FindNext(c) ' Look for next occurence of search string Loop While c.Address < firstAddress End If End With End Sub "Mick" wrote: The code you guys gave me does everything I want... almost. :) You guys are great, and I really appreciate your help, but I have a couple more questions... 1. Would it be possible to... a. Have it use the currently selected fill color for the highlighting? b. Prompt for a color to use for the highlighting? 2. Can I make it search the entire worksheet, without a hard-coded Range? Sub FindAndColour() Dim c as range Dim Findstr As String Findstr = InputBox("Enter search string") ' Enter your search string With Worksheets(1).Range("a1:D500") ' Change to reflect your search range Set c = .Find(Findstr, LookIn:=xlValues,Lookat:=XlWhole) If Not c Is Nothing Then firstAddress = c.Address Do c.EntireRow.Interior.ColorIndex = 4 ' Set row to green Set c = .FindNext(c) ' Look for next occurence of search string Loop While Not c Is Nothing And c.Address < firstAddress End If End With End Sub "Mick" wrote: Is it possible to program Excel's Find dialog so that it will search for all occurrences of a given string and highlight the rows containing the search string? What I need is a macro (or something) that will prompt me for a string to look for, then go through every cell and highlight the rows that contain the string I specified. Does anyone have a macro for this? If not, can someone please help me create a macro that will do this? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and highlight results macro
Better still ..
Dim MyRange As Range Set MyRange = Application.InputBox(prompt:="Enter Range", Type:=8) ' DataType is Range Colour = MyRange.Interior.ColorIndex "Toppers" wrote: Mick, I have just added another input box to ask for the colour code (number) and extended the range to whole worksheet using Cells. How do we know what is the currently selected fill colour? If you already have an coloured cell selected PRIOR to running the macro (this is the Activecell), then you could use: c.EntireRow.Interior.ColorIndex = ActiveCell.interior.ColorIndex Equally, you could enter the address of a coloured cell and use: ColAddr = InputBox("Enter Cell Address") ' Enter Cell address e.g A1 Colour = Range(ColAddr).Interior.ColorIndex HTH P.S. Sorry about the earlier code problem but both versions work in Excel 2003 ------------------------------------------------------------------------------------------- Sub FindAndColour() Dim c As Range Dim Findstr As String Dim Colour As Integer Findstr = InputBox("Enter search string") ' Enter your search string Colour = InputBox("Enter Colour code") ' Enter value for colour code With Worksheets(1).Cells ' Whole sheet Set c = .Find(Findstr, LookIn:=xlValues, Lookat:=xlWhole) If Not c Is Nothing Then firstAddress = c.Address Do c.EntireRow.Interior.ColorIndex = Colour ' Set row to green Set c = .FindNext(c) ' Look for next occurence of search string Loop While c.Address < firstAddress End If End With End Sub "Mick" wrote: The code you guys gave me does everything I want... almost. :) You guys are great, and I really appreciate your help, but I have a couple more questions... 1. Would it be possible to... a. Have it use the currently selected fill color for the highlighting? b. Prompt for a color to use for the highlighting? 2. Can I make it search the entire worksheet, without a hard-coded Range? Sub FindAndColour() Dim c as range Dim Findstr As String Findstr = InputBox("Enter search string") ' Enter your search string With Worksheets(1).Range("a1:D500") ' Change to reflect your search range Set c = .Find(Findstr, LookIn:=xlValues,Lookat:=XlWhole) If Not c Is Nothing Then firstAddress = c.Address Do c.EntireRow.Interior.ColorIndex = 4 ' Set row to green Set c = .FindNext(c) ' Look for next occurence of search string Loop While Not c Is Nothing And c.Address < firstAddress End If End With End Sub "Mick" wrote: Is it possible to program Excel's Find dialog so that it will search for all occurrences of a given string and highlight the rows containing the search string? What I need is a macro (or something) that will prompt me for a string to look for, then go through every cell and highlight the rows that contain the string I specified. Does anyone have a macro for this? If not, can someone please help me create a macro that will do this? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and highlight results macro
I didn't say it didn't work. I said it was redundant. It wasn't a
criticism of you or the author, microsoft. Just information for the OP who is trying to learn. -- Regards, Tom Ogilvy "Toppers" wrote in message ... Tom, Just FYI - in Excel 2003 both versions of the code work i.e. with/without the "Not C is Nothing". Logically (to me!) it should work even if it is redundant. "Tom Ogilvy" wrote: Just to add -- Most of the code provided can be found in the vba help on the findnext method. In both cases, since you are only marking the found cell and not removing the searched for text, the terminating condition only needs to be: Loop While c.Address < firstAddress rather than Loop While Not c Is Nothing And c.Address < firstAddress c will never be nothing. -- Regards, Tom Ogilvy "Toppers" wrote in message ... Hi, Sub FindAndColour() Dim c as range Dim Findstr As String Findstr = InputBox("Enter search string") ' Enter your search string With Worksheets(1).Range("a1:D500") ' Change to reflect your search range Set c = .Find(Findstr, LookIn:=xlValues,Lookat:=XlWhole) If Not c Is Nothing Then firstAddress = c.Address Do c.EntireRow.Interior.ColorIndex = 4 ' Set row to green Set c = .FindNext(c) ' Look for next occurence of search string Loop While Not c Is Nothing And c.Address < firstAddress End If End With HTH "Mick" wrote: Is it possible to program Excel's Find dialog so that it will search for all occurrences of a given string and highlight the rows containing the search string? What I need is a macro (or something) that will prompt me for a string to look for, then go through every cell and highlight the rows that contain the string I specified. Does anyone have a macro for this? If not, can someone please help me create a macro that will do this? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and highlight results macro
Thanks a lot topper! I think the problem with the first version not working
was probably my fault. So, there's no way to get the currently selected fill color, eh? And, no way to determine what the last cell (row and column) is? That's kindof bogus! :) Anyway, thanks again for the help. I really appreciate it! Regards, Mick "Toppers" wrote: Better still .. Dim MyRange As Range Set MyRange = Application.InputBox(prompt:="Enter Range", Type:=8) ' DataType is Range Colour = MyRange.Interior.ColorIndex "Toppers" wrote: Mick, I have just added another input box to ask for the colour code (number) and extended the range to whole worksheet using Cells. How do we know what is the currently selected fill colour? If you already have an coloured cell selected PRIOR to running the macro (this is the Activecell), then you could use: c.EntireRow.Interior.ColorIndex = ActiveCell.interior.ColorIndex Equally, you could enter the address of a coloured cell and use: ColAddr = InputBox("Enter Cell Address") ' Enter Cell address e.g A1 Colour = Range(ColAddr).Interior.ColorIndex HTH P.S. Sorry about the earlier code problem but both versions work in Excel 2003 ------------------------------------------------------------------------------------------- Sub FindAndColour() Dim c As Range Dim Findstr As String Dim Colour As Integer Findstr = InputBox("Enter search string") ' Enter your search string Colour = InputBox("Enter Colour code") ' Enter value for colour code With Worksheets(1).Cells ' Whole sheet Set c = .Find(Findstr, LookIn:=xlValues, Lookat:=xlWhole) If Not c Is Nothing Then firstAddress = c.Address Do c.EntireRow.Interior.ColorIndex = Colour ' Set row to green Set c = .FindNext(c) ' Look for next occurence of search string Loop While c.Address < firstAddress End If End With End Sub "Mick" wrote: The code you guys gave me does everything I want... almost. :) You guys are great, and I really appreciate your help, but I have a couple more questions... 1. Would it be possible to... a. Have it use the currently selected fill color for the highlighting? b. Prompt for a color to use for the highlighting? 2. Can I make it search the entire worksheet, without a hard-coded Range? Sub FindAndColour() Dim c as range Dim Findstr As String Findstr = InputBox("Enter search string") ' Enter your search string With Worksheets(1).Range("a1:D500") ' Change to reflect your search range Set c = .Find(Findstr, LookIn:=xlValues,Lookat:=XlWhole) If Not c Is Nothing Then firstAddress = c.Address Do c.EntireRow.Interior.ColorIndex = 4 ' Set row to green Set c = .FindNext(c) ' Look for next occurence of search string Loop While Not c Is Nothing And c.Address < firstAddress End If End With End Sub "Mick" wrote: Is it possible to program Excel's Find dialog so that it will search for all occurrences of a given string and highlight the rows containing the search string? What I need is a macro (or something) that will prompt me for a string to look for, then go through every cell and highlight the rows that contain the string I specified. Does anyone have a macro for this? If not, can someone please help me create a macro that will do this? |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and highlight results macro
This seems to work to get the cell range...
Dim lastCell As String lastCell = ActiveCell.SpecialCells(xlLastCell).Address With Worksheets(1).Range("a1:" + lastCell) "Toppers" wrote: Better still .. Dim MyRange As Range Set MyRange = Application.InputBox(prompt:="Enter Range", Type:=8) ' DataType is Range Colour = MyRange.Interior.ColorIndex "Toppers" wrote: Mick, I have just added another input box to ask for the colour code (number) and extended the range to whole worksheet using Cells. How do we know what is the currently selected fill colour? If you already have an coloured cell selected PRIOR to running the macro (this is the Activecell), then you could use: c.EntireRow.Interior.ColorIndex = ActiveCell.interior.ColorIndex Equally, you could enter the address of a coloured cell and use: ColAddr = InputBox("Enter Cell Address") ' Enter Cell address e.g A1 Colour = Range(ColAddr).Interior.ColorIndex HTH P.S. Sorry about the earlier code problem but both versions work in Excel 2003 ------------------------------------------------------------------------------------------- Sub FindAndColour() Dim c As Range Dim Findstr As String Dim Colour As Integer Findstr = InputBox("Enter search string") ' Enter your search string Colour = InputBox("Enter Colour code") ' Enter value for colour code With Worksheets(1).Cells ' Whole sheet Set c = .Find(Findstr, LookIn:=xlValues, Lookat:=xlWhole) If Not c Is Nothing Then firstAddress = c.Address Do c.EntireRow.Interior.ColorIndex = Colour ' Set row to green Set c = .FindNext(c) ' Look for next occurence of search string Loop While c.Address < firstAddress End If End With End Sub "Mick" wrote: The code you guys gave me does everything I want... almost. :) You guys are great, and I really appreciate your help, but I have a couple more questions... 1. Would it be possible to... a. Have it use the currently selected fill color for the highlighting? b. Prompt for a color to use for the highlighting? 2. Can I make it search the entire worksheet, without a hard-coded Range? Sub FindAndColour() Dim c as range Dim Findstr As String Findstr = InputBox("Enter search string") ' Enter your search string With Worksheets(1).Range("a1:D500") ' Change to reflect your search range Set c = .Find(Findstr, LookIn:=xlValues,Lookat:=XlWhole) If Not c Is Nothing Then firstAddress = c.Address Do c.EntireRow.Interior.ColorIndex = 4 ' Set row to green Set c = .FindNext(c) ' Look for next occurence of search string Loop While Not c Is Nothing And c.Address < firstAddress End If End With End Sub "Mick" wrote: Is it possible to program Excel's Find dialog so that it will search for all occurrences of a given string and highlight the rows containing the search string? What I need is a macro (or something) that will prompt me for a string to look for, then go through every cell and highlight the rows that contain the string I specified. Does anyone have a macro for this? If not, can someone please help me create a macro that will do this? |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and highlight results macro
Mick,
There are several ways of getting the last used row in a column: this is frequently used - Lastrow=Cells(rows.count,"A").end(xlup).row will get last non-blank row in column A (working upwards from the bottom row (65536). I used "Cells" so it does the whole sheet ( as I thought this what you wanted by saying "no hard-coded range" - wrong again!); but on limited testing this is very fast so it isn't an overhead to search all cells. "Mick" wrote: This seems to work to get the cell range... Dim lastCell As String lastCell = ActiveCell.SpecialCells(xlLastCell).Address With Worksheets(1).Range("a1:" + lastCell) "Toppers" wrote: Better still .. Dim MyRange As Range Set MyRange = Application.InputBox(prompt:="Enter Range", Type:=8) ' DataType is Range Colour = MyRange.Interior.ColorIndex "Toppers" wrote: Mick, I have just added another input box to ask for the colour code (number) and extended the range to whole worksheet using Cells. How do we know what is the currently selected fill colour? If you already have an coloured cell selected PRIOR to running the macro (this is the Activecell), then you could use: c.EntireRow.Interior.ColorIndex = ActiveCell.interior.ColorIndex Equally, you could enter the address of a coloured cell and use: ColAddr = InputBox("Enter Cell Address") ' Enter Cell address e.g A1 Colour = Range(ColAddr).Interior.ColorIndex HTH P.S. Sorry about the earlier code problem but both versions work in Excel 2003 ------------------------------------------------------------------------------------------- Sub FindAndColour() Dim c As Range Dim Findstr As String Dim Colour As Integer Findstr = InputBox("Enter search string") ' Enter your search string Colour = InputBox("Enter Colour code") ' Enter value for colour code With Worksheets(1).Cells ' Whole sheet Set c = .Find(Findstr, LookIn:=xlValues, Lookat:=xlWhole) If Not c Is Nothing Then firstAddress = c.Address Do c.EntireRow.Interior.ColorIndex = Colour ' Set row to green Set c = .FindNext(c) ' Look for next occurence of search string Loop While c.Address < firstAddress End If End With End Sub "Mick" wrote: The code you guys gave me does everything I want... almost. :) You guys are great, and I really appreciate your help, but I have a couple more questions... 1. Would it be possible to... a. Have it use the currently selected fill color for the highlighting? b. Prompt for a color to use for the highlighting? 2. Can I make it search the entire worksheet, without a hard-coded Range? Sub FindAndColour() Dim c as range Dim Findstr As String Findstr = InputBox("Enter search string") ' Enter your search string With Worksheets(1).Range("a1:D500") ' Change to reflect your search range Set c = .Find(Findstr, LookIn:=xlValues,Lookat:=XlWhole) If Not c Is Nothing Then firstAddress = c.Address Do c.EntireRow.Interior.ColorIndex = 4 ' Set row to green Set c = .FindNext(c) ' Look for next occurence of search string Loop While Not c Is Nothing And c.Address < firstAddress End If End With End Sub "Mick" wrote: Is it possible to program Excel's Find dialog so that it will search for all occurrences of a given string and highlight the rows containing the search string? What I need is a macro (or something) that will prompt me for a string to look for, then go through every cell and highlight the rows that contain the string I specified. Does anyone have a macro for this? If not, can someone please help me create a macro that will do this? |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and highlight results macro
Now, if I can somehow get the currently selected fill color I'll have
everything I need. :) If there's no way to do that, then I guess I'll have to make my own custom 'Find & Highlight' dialog to get the search parameters and highlight color. Thanks! "Toppers" wrote: Mick, There are several ways of getting the last used row in a column: this is frequently used - Lastrow=Cells(rows.count,"A").end(xlup).row will get last non-blank row in column A (working upwards from the bottom row (65536). I used "Cells" so it does the whole sheet ( as I thought this what you wanted by saying "no hard-coded range" - wrong again!); but on limited testing this is very fast so it isn't an overhead to search all cells. "Mick" wrote: This seems to work to get the cell range... Dim lastCell As String lastCell = ActiveCell.SpecialCells(xlLastCell).Address With Worksheets(1).Range("a1:" + lastCell) "Toppers" wrote: Better still .. Dim MyRange As Range Set MyRange = Application.InputBox(prompt:="Enter Range", Type:=8) ' DataType is Range Colour = MyRange.Interior.ColorIndex "Toppers" wrote: Mick, I have just added another input box to ask for the colour code (number) and extended the range to whole worksheet using Cells. How do we know what is the currently selected fill colour? If you already have an coloured cell selected PRIOR to running the macro (this is the Activecell), then you could use: c.EntireRow.Interior.ColorIndex = ActiveCell.interior.ColorIndex Equally, you could enter the address of a coloured cell and use: ColAddr = InputBox("Enter Cell Address") ' Enter Cell address e.g A1 Colour = Range(ColAddr).Interior.ColorIndex HTH P.S. Sorry about the earlier code problem but both versions work in Excel 2003 ------------------------------------------------------------------------------------------- Sub FindAndColour() Dim c As Range Dim Findstr As String Dim Colour As Integer Findstr = InputBox("Enter search string") ' Enter your search string Colour = InputBox("Enter Colour code") ' Enter value for colour code With Worksheets(1).Cells ' Whole sheet Set c = .Find(Findstr, LookIn:=xlValues, Lookat:=xlWhole) If Not c Is Nothing Then firstAddress = c.Address Do c.EntireRow.Interior.ColorIndex = Colour ' Set row to green Set c = .FindNext(c) ' Look for next occurence of search string Loop While c.Address < firstAddress End If End With End Sub "Mick" wrote: The code you guys gave me does everything I want... almost. :) You guys are great, and I really appreciate your help, but I have a couple more questions... 1. Would it be possible to... a. Have it use the currently selected fill color for the highlighting? b. Prompt for a color to use for the highlighting? 2. Can I make it search the entire worksheet, without a hard-coded Range? Sub FindAndColour() Dim c as range Dim Findstr As String Findstr = InputBox("Enter search string") ' Enter your search string With Worksheets(1).Range("a1:D500") ' Change to reflect your search range Set c = .Find(Findstr, LookIn:=xlValues,Lookat:=XlWhole) If Not c Is Nothing Then firstAddress = c.Address Do c.EntireRow.Interior.ColorIndex = 4 ' Set row to green Set c = .FindNext(c) ' Look for next occurence of search string Loop While Not c Is Nothing And c.Address < firstAddress End If End With End Sub "Mick" wrote: Is it possible to program Excel's Find dialog so that it will search for all occurrences of a given string and highlight the rows containing the search string? What I need is a macro (or something) that will prompt me for a string to look for, then go through every cell and highlight the rows that contain the string I specified. Does anyone have a macro for this? If not, can someone please help me create a macro that will do this? |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and highlight results macro
Mick,
You say " currently selected fill color " - by this, do you mean you a cell actual selected? If so, then: ActiveCell.Interior.ColorIndex will give you the colour. If you don't have a cell selected, then I can't see how you can get the colour as your reply also suggests there is (can be ) more than one colour. One possible way is to have a palette of cells with your colours and select one prior to invoking your macro. Or simply store the "currently selected colour" - however this is determined - and then recall in your macro. I've run out of ideas! Hope you find a solution. "Mick" wrote: Now, if I can somehow get the currently selected fill color I'll have everything I need. :) If there's no way to do that, then I guess I'll have to make my own custom 'Find & Highlight' dialog to get the search parameters and highlight color. Thanks! "Toppers" wrote: Mick, There are several ways of getting the last used row in a column: this is frequently used - Lastrow=Cells(rows.count,"A").end(xlup).row will get last non-blank row in column A (working upwards from the bottom row (65536). I used "Cells" so it does the whole sheet ( as I thought this what you wanted by saying "no hard-coded range" - wrong again!); but on limited testing this is very fast so it isn't an overhead to search all cells. "Mick" wrote: This seems to work to get the cell range... Dim lastCell As String lastCell = ActiveCell.SpecialCells(xlLastCell).Address With Worksheets(1).Range("a1:" + lastCell) "Toppers" wrote: Better still .. Dim MyRange As Range Set MyRange = Application.InputBox(prompt:="Enter Range", Type:=8) ' DataType is Range Colour = MyRange.Interior.ColorIndex "Toppers" wrote: Mick, I have just added another input box to ask for the colour code (number) and extended the range to whole worksheet using Cells. How do we know what is the currently selected fill colour? If you already have an coloured cell selected PRIOR to running the macro (this is the Activecell), then you could use: c.EntireRow.Interior.ColorIndex = ActiveCell.interior.ColorIndex Equally, you could enter the address of a coloured cell and use: ColAddr = InputBox("Enter Cell Address") ' Enter Cell address e.g A1 Colour = Range(ColAddr).Interior.ColorIndex HTH P.S. Sorry about the earlier code problem but both versions work in Excel 2003 ------------------------------------------------------------------------------------------- Sub FindAndColour() Dim c As Range Dim Findstr As String Dim Colour As Integer Findstr = InputBox("Enter search string") ' Enter your search string Colour = InputBox("Enter Colour code") ' Enter value for colour code With Worksheets(1).Cells ' Whole sheet Set c = .Find(Findstr, LookIn:=xlValues, Lookat:=xlWhole) If Not c Is Nothing Then firstAddress = c.Address Do c.EntireRow.Interior.ColorIndex = Colour ' Set row to green Set c = .FindNext(c) ' Look for next occurence of search string Loop While c.Address < firstAddress End If End With End Sub "Mick" wrote: The code you guys gave me does everything I want... almost. :) You guys are great, and I really appreciate your help, but I have a couple more questions... 1. Would it be possible to... a. Have it use the currently selected fill color for the highlighting? b. Prompt for a color to use for the highlighting? 2. Can I make it search the entire worksheet, without a hard-coded Range? Sub FindAndColour() Dim c as range Dim Findstr As String Findstr = InputBox("Enter search string") ' Enter your search string With Worksheets(1).Range("a1:D500") ' Change to reflect your search range Set c = .Find(Findstr, LookIn:=xlValues,Lookat:=XlWhole) If Not c Is Nothing Then firstAddress = c.Address Do c.EntireRow.Interior.ColorIndex = 4 ' Set row to green Set c = .FindNext(c) ' Look for next occurence of search string Loop While Not c Is Nothing And c.Address < firstAddress End If End With End Sub "Mick" wrote: Is it possible to program Excel's Find dialog so that it will search for all occurrences of a given string and highlight the rows containing the search string? What I need is a macro (or something) that will prompt me for a string to look for, then go through every cell and highlight the rows that contain the string I specified. Does anyone have a macro for this? If not, can someone please help me create a macro that will do this? |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find and highlight results macro
I want to use the fill color that is currently selected in the formatting
toolbar of the Excel main widow. I thought there might be some way to get this color--maybe using the Application object? Actually, I'd prefer is to display a color palette in the same dialog that prompts the user for the text to search for. Or, if that's not possible, to display a color selector dialog right after the user specifies the text to search for. The idea is to have a macro that will let the user highlight rows with one color that contain a specified text string, then run the macro again and highlight other rows with a different color. "Toppers" wrote: Mick, You say " currently selected fill color " - by this, do you mean you a cell actual selected? If so, then: ActiveCell.Interior.ColorIndex will give you the colour. If you don't have a cell selected, then I can't see how you can get the colour as your reply also suggests there is (can be ) more than one colour. One possible way is to have a palette of cells with your colours and select one prior to invoking your macro. Or simply store the "currently selected colour" - however this is determined - and then recall in your macro. I've run out of ideas! Hope you find a solution. "Mick" wrote: Now, if I can somehow get the currently selected fill color I'll have everything I need. :) If there's no way to do that, then I guess I'll have to make my own custom 'Find & Highlight' dialog to get the search parameters and highlight color. Thanks! "Toppers" wrote: Mick, There are several ways of getting the last used row in a column: this is frequently used - Lastrow=Cells(rows.count,"A").end(xlup).row will get last non-blank row in column A (working upwards from the bottom row (65536). I used "Cells" so it does the whole sheet ( as I thought this what you wanted by saying "no hard-coded range" - wrong again!); but on limited testing this is very fast so it isn't an overhead to search all cells. "Mick" wrote: This seems to work to get the cell range... Dim lastCell As String lastCell = ActiveCell.SpecialCells(xlLastCell).Address With Worksheets(1).Range("a1:" + lastCell) "Toppers" wrote: Better still .. Dim MyRange As Range Set MyRange = Application.InputBox(prompt:="Enter Range", Type:=8) ' DataType is Range Colour = MyRange.Interior.ColorIndex "Toppers" wrote: Mick, I have just added another input box to ask for the colour code (number) and extended the range to whole worksheet using Cells. How do we know what is the currently selected fill colour? If you already have an coloured cell selected PRIOR to running the macro (this is the Activecell), then you could use: c.EntireRow.Interior.ColorIndex = ActiveCell.interior.ColorIndex Equally, you could enter the address of a coloured cell and use: ColAddr = InputBox("Enter Cell Address") ' Enter Cell address e.g A1 Colour = Range(ColAddr).Interior.ColorIndex HTH P.S. Sorry about the earlier code problem but both versions work in Excel 2003 ------------------------------------------------------------------------------------------- Sub FindAndColour() Dim c As Range Dim Findstr As String Dim Colour As Integer Findstr = InputBox("Enter search string") ' Enter your search string Colour = InputBox("Enter Colour code") ' Enter value for colour code With Worksheets(1).Cells ' Whole sheet Set c = .Find(Findstr, LookIn:=xlValues, Lookat:=xlWhole) If Not c Is Nothing Then firstAddress = c.Address Do c.EntireRow.Interior.ColorIndex = Colour ' Set row to green Set c = .FindNext(c) ' Look for next occurence of search string Loop While c.Address < firstAddress End If End With End Sub "Mick" wrote: The code you guys gave me does everything I want... almost. :) You guys are great, and I really appreciate your help, but I have a couple more questions... 1. Would it be possible to... a. Have it use the currently selected fill color for the highlighting? b. Prompt for a color to use for the highlighting? 2. Can I make it search the entire worksheet, without a hard-coded Range? Sub FindAndColour() Dim c as range Dim Findstr As String Findstr = InputBox("Enter search string") ' Enter your search string With Worksheets(1).Range("a1:D500") ' Change to reflect your search range Set c = .Find(Findstr, LookIn:=xlValues,Lookat:=XlWhole) If Not c Is Nothing Then firstAddress = c.Address Do c.EntireRow.Interior.ColorIndex = 4 ' Set row to green Set c = .FindNext(c) ' Look for next occurence of search string Loop While Not c Is Nothing And c.Address < firstAddress End If End With End Sub "Mick" wrote: Is it possible to program Excel's Find dialog so that it will search for all occurrences of a given string and highlight the rows containing the search string? What I need is a macro (or something) that will prompt me for a string to look for, then go through every cell and highlight the rows that contain the string I specified. Does anyone have a macro for this? If not, can someone please help me create a macro that will do this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formating Highlight Results | Excel Worksheet Functions | |||
how to highlight the results of a search string in a worksheet? | Excel Worksheet Functions | |||
Cant readliy see the FIND results? Is there a way to highlight bet | Excel Discussion (Misc queries) | |||
Highlight Search Results | Excel Discussion (Misc queries) | |||
Highlight FIND Results | Excel Discussion (Misc queries) |