Macro Inquiry - Beginner Support

Re: Macro Inquiry - Beginner Support

Maze,

I'll adjust the code I posted to try and answer your questions.

Sub HighLight() 
     
     '  Local Variables
    Dim rngData          As Range, rngTgtCol        As Range 
    Dim cell          As Range 
    Dim strSearchString  As String 
     
     '  Create range object for data in table ( for this example needs to be Col A )
    Set rngData = Worksheets("Sheet1").Range("A2", Worksheets("Sheet1").Range("A65536").End(xlUp)) 
     
     '  Remove all interior color in data range
    rngData.Interior.ColorIndex = xlNone 
     
     '  Now set parameters
    strSearchString = "WITHIN FOOTPRINT" 
    Set rngTgtCol = Worksheets("Sheet1").Range("E1") 
     
     '  Search the table of data
    For Each cell In rngData 
        If instr(cell.Offset(0, rngTgtCol.Column - 1), strSearchString)>0 Then 
             '  The following line highlights cell in Col A
            cell.Interior.ColorIndex = 3 
             '  The following highlights from Col A to the last Col with information
            cell.Range("A1",cell.range("IV1").end(xlToLeft)).Interior.ColorIndex = 3 
        End If 
    Next cell 
     
End Sub

If you wanted to test for a "BLANK" then

If cell = "" Then

The only issue with the above is what if the cell has a BLANK space ( someone who wanted to blank the cell and used the spacebar ) so a better test for a blank cell would be

If trim(cell) = "" Then

Re: Macro Inquiry - Beginner Support

Maze,

Ok that was my fault replace that one line of code with the following:

cell.Range("A1", Range("IV1").End(xlToLeft)).interior.colorindex = 3

You seem to have grasped the concept of what I've done. Try doing a help on END that'll give you what the END does. Then IV1 that's the last column available on a worksheet, that with the information from END you'll know why I reference it.

Re: Macro Inquiry - Beginner Support

Maze,

In looking at the workbook that you posted you had a solid table of data. Meaning that all cells had values from A To F.

Now if you add a new column of data but leave the cells blank the end method will find the first cell with data to the left of IV hence if the new columng "G" is blank the first found cell is still "F"

Now if your table will not have data in all fields, i.e. some rows will have data A to E, or A to D, or A to G then you need to

1. Hard code the the G range. ( But what happens when you add a column H of data )
2. Determine the width of the data table by the column headers. ( This will take into account if you add 1 or n number of columns )