Find Specific word and then highlight range of cells

Posted on 2013-09-25
Medium Priority
Last Modified: 2013-09-25
I need to be able to find either a word or a word that starts with xx and then highlight a specific range.

I have this code but it only highlights the word. Could someone assist please. I need to be able to Highlight the row from A to R

Dim vCells As Range
    For Each vCells In Range("A1:A2000").Cells
        If Left(vCells.Text, 2) = "Ev" Then
            vCells.Interior.ColorIndex = 43
            vCells.Borders.LineStyle = xlContinuous
            vCells.Borders.Weight = xlThin
        End If
    Next vCells

many thanks
Question by:Jagwarman
LVL 35

Assisted Solution

by:[ fanpages ]
[ fanpages ] earned 800 total points
ID: 39520713

Please change you existing code to read:

Public Sub Q_28249024()

  Dim vCells                                            As Range
  Range("A1:R2000").Interior.ColorIndex = xlNone        ' *** ADDED
  Range("A1:R2000").Borders.LineStyle = xlNone          ' *** ADDED
  For Each vCells In Range("A1:A2000").Cells
      If Left(vCells.Text, 2) = "Ev" Then
         Range(Cells(vCells.Row, "A"), Cells(vCells.Row, "R")).Interior.ColorIndex = 43             ' *** CHANGED
         Range(Cells(vCells.Row, "A"), Cells(vCells.Row, "R")).Borders.LineStyle = xlContinuous     ' *** CHANGED
         Range(Cells(vCells.Row, "A"), Cells(vCells.Row, "R")).Borders.Weight = xlThin              ' *** CHANGED
     End If
  Next vCells

End Sub

Open in new window

Please note that I have added two lines to "reset" all the cells in the range [A1:R2000] before the loop begins.


LVL 54

Expert Comment

ID: 39520718

pls try

Dim vCells As Range
    For Each vCells In Range("A1:A2000").Cells
        If Left(vCells.Text, 2) = "Ev" Then
            vCells.Resize(1, 18).Interior.ColorIndex = 43
            vCells.Resize(1, 18).Borders.LineStyle = xlContinuous
            vCells.Resize(1, 18).Borders.Weight = xlThin
        End If
    Next vCells

Open in new window

LVL 35

Accepted Solution

Rob Henson earned 1200 total points
ID: 39520816
How about adopting Conditional Formatting?

The formatting can use a formula to determine whether a string is found within the cell.

The string to look for can be in another cell and referred to in the formula.

Rob H

Author Closing Comment

ID: 39520908
Thank you

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question