Prev: object required
Next: Program stops (creating pivot table) when too many records
From: baha17 on 4 Mar 2010 20:32
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
From: baha17 on 5 Mar 2010 01:51
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
 | 
Pages: 1
Prev: object required
Next: Program stops (creating pivot table) when too many records