Solved Help with highlighting cells (1 Viewer)

lookforsmt

Registered User.
Local time
Today, 12:49
Joined
Dec 26, 2011
Messages
620
Hello everyone
i am stuck with the below vba code. This code helps me to highlight a single column, however, i am trying to give it a range in s1 as "A1:AX100" and it gives me an error. Below is the code and any suggestions.
Code:
Sub HighlightCells()
    Dim s1 As Worksheet, s2 As Worksheet
    Dim lr As Long, lr2 As Long
    Dim i As Long, j As Long
    Set s1 = Sheets("Numbering")
    Set s2 = Sheets("ABC")
    lr = s1.Range("A" & Rows.Count).End(xlUp).Row
    lr2 = s2.Range("C" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = False
    For i = 2 To lr
        For j = 2 To lr2
            If InStr(s1.Range("A" & i), s2.Range("C" & j)) > 0 Then
                s1.Range("A" & i).Interior.ColorIndex = 6
            End If
        Next j
    Next i
    Application.ScreenUpdating = True
    MsgBox "Review Completed"
End Sub
 

Isaac

Lifelong Learner
Local time
Today, 00:49
Joined
Mar 14, 2017
Messages
2,801
What error does it give you?
On which line of code?
Can you post a sample sheet that when you run this code you get that same error?
 

Minty

AWF VIP
Local time
Today, 08:49
Joined
Jul 26, 2013
Messages
7,530
A range can (normally) have 2 components, something like Range("A1:C3")

You are only giving it the first part which will select a single cell?
 

lookforsmt

Registered User.
Local time
Today, 12:49
Joined
Dec 26, 2011
Messages
620
Thank you for your reply. I am getting error on line "lr" as i have changed the range from "A" to "A1:AX100"
i am unable to upload xlsm files
 

Isaac

Lifelong Learner
Local time
Today, 00:49
Joined
Mar 14, 2017
Messages
2,801
Thank you for your reply. I am getting error on line "lr" as i have changed the range from "A" to "A1:AX100"
i am unable to upload xlsm files
You can zip them and upload the zip file
You forgot to mention WHAT the error is.
 

lookforsmt

Registered User.
Local time
Today, 12:49
Joined
Dec 26, 2011
Messages
620
i dont have winzip on my laptop. i tried to download its paid stuff
below is the error

Run-time error '1004'
Method 'Range' of object'_worksheet failed
 

Isaac

Lifelong Learner
Local time
Today, 00:49
Joined
Mar 14, 2017
Messages
2,801
i dont have winzip on my laptop. i tried to download its paid stuff
below is the error

Run-time error '1004'
Method 'Range' of object'_worksheet failed
With the xlsm closed, right-click on it, and choose Send To > Compressed Folder. That will create the zip file.
 

Minty

AWF VIP
Local time
Today, 08:49
Joined
Jul 26, 2013
Messages
7,530
Thank you for your reply. I am getting error on line "lr" as i have changed the range from "A" to "A1:AX100"
i am unable to upload xlsm files
You can't set a long integer to a range count in that fashion. In fact, I don't think you can use that xlUp function against a full range.
 

Isaac

Lifelong Learner
Local time
Today, 00:49
Joined
Mar 14, 2017
Messages
2,801
I am getting error on line "lr"
Does that mean you are getting it on this line?
Code:
lr = s1.Range("A" & Rows.Count).End(xlUp).Row
Because that code looks correct. EXCEPT..you shouldn't just say Rows.Count, you should say s1.Rows.Count
So if the error IS on that line, try:
Code:
lr = s1.Range("A" & s1.Rows.Count).End(xlUp).Row

This would be much easier if you would upload the zip. Follow my instructions to send to compressed folder. Free functionality included with all Windows since I've ever known.
 

lookforsmt

Registered User.
Local time
Today, 12:49
Joined
Dec 26, 2011
Messages
620
thank you for the code. Now i am getting error on next line as below
If InStr(s1.Range("A1:AX100" & i), s2.Range("C" & j)) > 0 Then

below is the error
Run-time error "13"
Type Mismatch

i tried using your code but it still gives me error
 

Isaac

Lifelong Learner
Local time
Today, 00:49
Joined
Mar 14, 2017
Messages
2,801
thank you for the code. Now i am getting error on next line as below
If InStr(s1.Range("A1:AX100" & i), s2.Range("C" & j)) > 0 Then

below is the error
Run-time error "13"
Type Mismatch

i tried using your code but it still gives me error
A1:AX100" & i looks a little "off". Do you really want to concatenate a fixed number, 100, with an additional string, whatever i represents??

Maybe you can explain from the beginning what you want to do and we can suggest code to do it. It might be a shorter path to a solution rather than taking what might be an indefinite number of errors and trying to correct them (without even knowing your objective). A precise and thorough statement of what your intention is, with this code?
 

lookforsmt

Registered User.
Local time
Today, 12:49
Joined
Dec 26, 2011
Messages
620
i have managed to upload the excel., can you have a look at it pls. thanks
 

Attachments

  • Test ABC_1.zip
    22.9 KB · Views: 14

Isaac

Lifelong Learner
Local time
Today, 00:49
Joined
Mar 14, 2017
Messages
2,801
Ok, so when the code fails you want to go into Debug mode and use the Immediate window to examine the value of the various bits of code that are trying to execute.
In this case, you have this code for the string value of the first range in the InStr(): ("A" & s1.Rows.Count & i)
Which evaluates in the immediate window, to: A10485762. Problem is, there aren't 10 million rows on an Excel sheet.

Perhaps you meant for it to be: ("A" & i) ?

And in keeping with this change, the next line of code:
s1.Range("A" & s1.Rows.Count & i).Interior.ColorIndex = 6

Should probably be:
s1.Range("A" & i).Interior.ColorIndex = 6

When I make these changes, the code completes with no errors. Whether it's doing what you actually want you'll have to determine.

HTH.
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:49
Joined
Sep 21, 2011
Messages
7,167
@Isaac,
I think the o/p is trying to search a whole range for whatever is in s2.Range("C" & j)
Firstly I think the .Value property needs to be used for that cell.
Secondly I think each cell in that range needs to be compared one at a time?
 

lookforsmt

Registered User.
Local time
Today, 12:49
Joined
Dec 26, 2011
Messages
620
i am not getting any errors after the change, however it only highlight the 1st column in sheet "Numbering". Rest of the columns dont get highlighted
 

Isaac

Lifelong Learner
Local time
Today, 00:49
Joined
Mar 14, 2017
Messages
2,801
@Isaac,
I think the o/p is trying to search a whole range for whatever is in s2.Range("C" & j)
Firstly I think the .Value property needs to be used for that cell.
Secondly I think each cell in that range needs to be compared one at a time?
I am admittedly not nearly as good as some at the art of understanding what OPs want to do based on deriving it from their code - especially from non-working code, without a thorough and well written explanation, so I'll take your word on that- you may well be right.
Technically anything that evaluates to a Range (including, I think, a call to Cells), should not need .Value as it's the default property ... But I am actually a big fan of NOT "dropping" things from code just because they are default properties for several reasons...so I'm with you on that, either way.

I've corrected OP code to the point where it executes at least, but I may bow out if understanding and insight does not come to me soon as to what their full intentions are.
 

lookforsmt

Registered User.
Local time
Today, 12:49
Joined
Dec 26, 2011
Messages
620
i just wanted to explain a bit more on the attached excel.
Sheet "ABC" is where i input the numbers and Sheet "Numbering" it should highlight the numbers which appear from ABC.

the code is error free.:
But it only highlights column A from sheet "Numbering"
It also highlighted some numbers which were not part of sheet ABC

i am hoping that i have explained this.
Thanks
 

Isaac

Lifelong Learner
Local time
Today, 00:49
Joined
Mar 14, 2017
Messages
2,801
So you want it to look through all cells with data on Numbering. Take the value of the cell, and, if the value of the cell matches the value of any cell on ABC (in column C), then color the Numbering cell.

Is that right?
 

lookforsmt

Registered User.
Local time
Today, 12:49
Joined
Dec 26, 2011
Messages
620
yes, exactly this is what i am looking for. sorry for the initial incomplete explanation.
 

Isaac

Lifelong Learner
Local time
Today, 00:49
Joined
Mar 14, 2017
Messages
2,801
Code:
Sub MattWinter()
Dim wsABC As Worksheet, wsNumbering As Worksheet
Dim rngNumbering As Range
Dim lastrowNumbering As Long, rngFound As Range
Set wsABC = ThisWorkbook.Worksheets("ABC")
Set wsNumbering = ThisWorkbook.Worksheets("Numbering")
lastrowNumbering = wsNumbering.Range("A" & wsNumbering.Rows.Count).End(xlUp).Row

For Each rngNumbering In wsNumbering.Range("A1:Ax" & lastrowNumbering)
    Set rngFound = wsABC.Range("c:c").Find(what:=rngNumbering.Value, lookat:=xlWhole)
    If Not rngFound Is Nothing Then
        'we found a match, color the numbering cell
        rngNumbering.Interior.ColorIndex = 6
    End If
Next rngNumbering

End Sub

Note that the names of the variables make it super easy to follow the intent of the code, almost regardless of which line you are on or whether you even read the Declarations section. I know you see a lot of code out there with super short variable names, but my preference/recommendation is to make them extremely descriptive - even if they end up a bit longer. Actually, it helps me a lot too when writing the code myself, but also others following it.

Also, in many cases (like this), you have a choice, you can either code using a strict numerical-loop construct (like for x = 2 to variable), or, you can define a range, complete with its own address, and loop through "for each range". To me the latter is easier to follow.

Finally, using the Find method, you gain speed here by avoid the 2nd (nested) loop.
 
Last edited:

Users who are viewing this thread

Top Bottom