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

stressler

Board Regular
Joined
Jun 25, 2014
Messages
95
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?
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,575
Office Version
  1. 2013
Platform
  1. Windows
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.

Code:
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.
 
Last edited:

stressler

Board Regular
Joined
Jun 25, 2014
Messages
95
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,575
Office Version
  1. 2013
Platform
  1. Windows
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?
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,575
Office Version
  1. 2013
Platform
  1. Windows
My mistake.
Try this:
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,774
Messages
5,638,268
Members
417,019
Latest member
PKDP

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top