Excel macro needed to locate and highlight text found in specific cell on another sheet

[stressler]

I have posted several other problems on here, but feel I'm being too complicated in my ask so I am breaking this down to a single step at a time.

I have an Excel workbook with 2 sheets, one named Report and one named Summary. I have a list of cycle names on the Summary tab in cells A2:A50. What I would like to do is run a macro that searches column A on my Report tab for the cycle name found on my Summary tab in cell A2. When the text that's located on the Summary tab in cell A2 is found on the Report tab, I want the entire row highlighted. I want the macro to continue searching all rows until the last row has been searched for the matching text from Summary tab cell A2 and the last row has been highlighted when it's found on Report Sheet in column A.

Then I would like to be able to continue running additional macros for the cycle name on the Summary tab in cell A3 highlighting all the matching rows on the Report tab a different color than the rows highlighted from the first macro, once that macro is done running, I want to be able to run a macro for the cycle name on the Summary tab in cell A4 and so on and so forth until I've been able to search and highlight all rows on the Report tab that match the text found in all my cycle name fields from the Summary tab.

I want each macro to be able to be run independently of each other. Right now I would like to set it so that I can just hit Run on the Macro tab and run each one individually.

Can this be done?

 

[My Aswer Is This]

Let try this:

Instead of needing more then one Macro this script will allow you to choose what row in sheet "Summary" Column "A" the value you want to look for is in.
And then you need to enter the color index number you want to use as the interior color. Remember there are only about 56 choices. 1 to 56

The script will search Column "A" of sheet named "Report" for the value.

Sub Test()
Application.ScreenUpdating = False
Dim i As Long
Dim ans As Long
Dim r As Long
Dim c As Long
r = InputBox("Enter Row Number")
c = InputBox("What color")
ans = Sheets("Summary").Cells(r, "A").Value
Dim Lastrow As Long
Lastrow = Sheets("Report").Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To Lastrow
        If Sheets("Report").Cells(i, 1).Value = ans Then Sheets("Report").Rows(i).Interior.ColorIndex = c
    Next

Application.ScreenUpdating = True
End Sub

If this does what you want I could write a script to search for all the values at once.

 

[stressler]

Good morning and thanks for the macro above. When I run the macro. I enter my row number, but when I enter the color number (I've tried several), I get a Type mismatch error. Any thoughts?

 

[My Aswer Is This]

You need to enter a number between 1 to 56 any number higher then 56 will error out.

Good morning and thanks for the macro above. When I run the macro. I enter my row number, but when I enter the color number (I've tried several), I get a Type mismatch error. Any thoughts?

 

[stressler]

I only used numbers between 1 and 56, nothing higher than 56.

 

[My Aswer Is This]

My mistake.
Try this:

Sub Test()
'Modified 3-20-2017 1:10 PM EDT
Application.ScreenUpdating = False
Dim i As Long
Dim ans As String
Dim r As Long
Dim c As Long
r = InputBox("Enter Row Number")
c = InputBox("What color")
ans = Sheets("Summary").Cells(r, "A").Value
Dim Lastrow As Long
Lastrow = Sheets("Report").Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To Lastrow
        If Sheets("Report").Cells(i, 1).Value = ans Then Sheets("Report").Rows(i).Interior.ColorIndex = c
    Next
Application.ScreenUpdating = True
End Sub