[RESOLVED] How to highlight a cell ?

[abhijit]

My Excel Macro
++++++++++++++

I have written a macro in Excel that allows me to check for the differences in outputs of two queries.
Now my boss wants me to highlight the cells instead of changing the font to bold and red.

I wrote some code after having experimented with excel a bit, but thats not working.

Here is my code.

Sub MarkUniques()
    
    'Date       - 05-August-2005
    'Author     - Abhijit Shrikhande
    'Purpose    - To check the output from two different result sets.
    '           - Works by highlighting the cells that are different.
    
    Dim iColCounter As Integer
    Dim iColsToCheck As Integer
    Dim lRowCounter As Long
    Dim lRowsToCheck As Long
    
    
'Find out number of columns
    iColCounter = 1
    Do While Trim(Sheet1.Cells(1, iColCounter)) <> ""
        iColCounter = iColCounter + 1
    Loop
        iColsToCheck = iColCounter - 1
 
'Find out number of rows
    lRowCounter = 1
    Do While Trim(Sheet1.Cells(lRowCounter, 1)) <> ""
        lRowCounter = lRowCounter + 1
    Loop
        lRowsToCheck = lRowCounter - 1
 
'Start from 2nd Row, 1st Column
    For lRowCounter = 2 To lRowsToCheck
        For iColCounter = 1 To iColsToCheck
            If Worksheets(1).Cells(lRowCounter, iColCounter) <> Worksheets(2).Cells(lRowCounter, iColCounter) Then
                    Worksheets(2).Cells(lRowCounter, iColCounter).Font.Bold = True
                    Worksheets(2).Cells(lRowCounter, iColCounter).Font.Color = vbRed
                    '<--'Code fails at the next line -->
                    Worksheets(2).Range(lRowCounter, iColCounter).Select
                    With Selection.Interior
                        .ColorIndex = 6
                        .Pattern = xlSolid
                    End With
            End If
        Next iColCounter
    Next lRowCounter
End Sub

Cheers,
Abhijit

[Bruce Fox]

On a different note; As an alternate to looping you can determine the last Row/Column used using UsedRange like:

intRowCount = objExcel.ActiveSheet.UsedRange.Rows.Count 'Capture the number of USED Rows
    intColCount = objExcel.ActiveSheet.UsedRange.Columns.Count  'Capture the number of USED Columns

Here is an example: http://www.vbforums.com/showthread.php?threadid=306042

Bruce.

[Bruce Fox]

It the use of Range that is the problem in this case, use Cells instead.

I have successfully used Worksheets(2).Cells(lRowCounter, iColCounter).Select

[abhijit]

Thanks mate,
Got that done now.

Btw - Do you know how to unselect a cell?