Match the value and highlight offset

B

baha17

Hi,
I have a list on range for sheet2.range("A1:A1000"). If any cell value
in sheet1 matches the value in my original list in Sheet2, I want to
highlight the cell.offset(0,-1) and cell.offset(0,-2). I got lost in
the for each code:) Is there someone can guide me through with a
code? I rephrase my problem for clarification. I basically want to
highlight the left two cells if any cell in sheet1 matches my list in
sheet2.Thank you very much for your help.
Have a nice day
Baha
 
B

baha17

Hello everyone,
Actually I made a solution to my question as below code.However if
"ba" range is larger the code works very slow.Any suggestions?
Thank again
Baha




Sub mark_the_match()
Application.ScreenUpdating = False

Dim r As Range
Dim a As Variant
Dim ba As Range
Set ba = Sheet1.Range("J1:U100") ' if this range is longer,code works
very slow

Sheet1.Select
'Range("B:B").Select
' Columns("B:B").Select
' Selection.Interior.ColorIndex = xlNone
'Sheet1.Select

For Each r In ba
If Application.WorksheetFunction.CountIf(Sheet2.Range("C:C"), r) = 1
Then
a = a + 1
r.Offset(0, -1).Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
End If
Next r
Sheet1.Range("A1") = a
Application.ScreenUpdating = True



End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top