First, I had trouble with your variable names. They may make sense to you today
and tomorrow, but there will come a time when you have to make a change and you
might have trouble remembering the difference between ws1 and ws2 (for example).
Second, you can check for a match inside your code--you don't have to actually
use a cell on the worksheet. If you need the value in the worksheet, then you
can uncomment that little portion that adds the formula.
If you're only looking to shade a cell, then this might work for you.
This compiled for me, but I didn't test it.
Option Explicit
Sub testme()
Dim CurrWkbkName As String
Dim PrevWkbkName As String
Dim PrevDayStr As String
Dim CurDayStr As String
Dim CurSht As Worksheet
Dim PrevSht As Worksheet
Dim myCell As Range
Dim res As Variant
PrevDayStr = "Previousday"
CurDayStr = "Currentday"
CurrWkbkName = HistoryDialog.Active_Workbook1
PrevWkbkName = HistoryDialog.Active_Workbook2
'Current sheet
Set CurSht = Workbooks(CurrWkbkName).Worksheets(CurDayStr)
'previous daysheet
Set PrevSht = Workbooks(PrevWkbkName).Worksheets(PrevDayStr)
With CurSht
' .Range("BL2:BL" & .Cells(.Rows.Count, "A").End(xlUp).Row).Formula _
' = "=isna(match(a2," & _
' PrevSht.Range("a:A").Address(external:=True) & ",0))"
For Each myCell In .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
res = Application.Match(myCell.Value, PrevSht.Range("a:a"), 0)
If IsError(res) Then
'no match, so it's a new CR
With myCell.Interior
.ColorIndex = 4
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Else
'do nothing--not even reset colors???
End If
Next myCell
End With
End Sub
====
And as an aside, I like my matches to show True and my mismatches to show False
(I'm quite anal-retentive!).
I'd use this line:
.Range("BL2:BL" & .Cells(.Rows.Count, "A").End(xlUp).Row).Formula _
= "=isnumber(match(a2," & _
PrevSht.Range("a:A").Address(external:=True) & ",0))"
(=Isnumber() instead of =isna().)
Pam wrote:
>
> Hi
>
> I have two work books which each one has two sheets with the same
> name, the book name(File name.xls changes each day by date).
>
> I am comparing two books and two sheets which work out fine, but when
> I try to color code cells in the current sheet it color codes the
> cells in the previous sheet which is the correct name but wrong book,
> example Let's say book 1 and 2 have sheet names Previous and Current.
>
> I compare book1 Current sheet to book2 Previous sheet(book2 also
> has a Current sheet from previous day because it is being compared as
> ongoing analysis.
>
> The problem I am having is when I try to color code book1 Current
> sheet it color codes book2 Current sheet which is the previous day.
>
> My logic for outcome of MATCH seems to be fine.
>
> Here is a snippet of my code. It may be something obvious but I can't
> spot it.
>
> Dim workbook_name As String
> Dim workbook_name2 As String
> Dim Ac As String
> Dim Bc As String
> Ac = "Previousday"
> Bc = "Currentday"
> workbook_name = HistoryDialog.Active_Workbook1
> workbook_name2 = HistoryDialog.Active_Workbook2
> Dim WS1 As Worksheet
> Dim WS2 As Worksheet
> Dim rwindex As Integer
> rwindex = 2 'Start at row two
> X = 1
> Dim colindex As String
> colindex = 1
> Dim colcomp As Integer
> colcomp = 64 this is the column I put outcome from MATCH
>
> Windows(workbook_name).Activate
> Windows(workbook_name2).Activate
>
> Set WS1 = Workbooks(workbook_name).Worksheets(Bc) 'Current sheet
> Set WS2 = Workbooks(workbook_name2).Worksheets(Ac) 'Previous Day
> sheet
>
> Sheets(Bc).Select
> While (WS1.Cells(rwindex, X) <> "")
> rwindex = rwindex + 1
> Count = Count + 1
> n = Count 'This is the counter so forumulas won't goto infinity
> Wend
>
> WS1.Range("BL2").Resize(n, 1).Formula = "=ISNA(MATCH(A2,'[" &
> workbook_name2 & "]Previousday!A:A,0))"
> Range("BL2").Select
> MsgBox ("There is a Match****")
> ***The above seems to work out fine****
>
> rwindex = 2 reset counter
> While (Cells(rwindex, X) <> "")
> If WS1.Cells(rwindex, colcomp).Value = True Then
>
> hold = WS1.Cells(rwindex, colcomp).Value 'Give me correct data
>
> MsgBox ("What is in this cell" & hold) 'error handling will come out
> later
>
> MsgBox ("This is a new CR") 'error handling will come out later
>
> WS1.Range("A2" & rwindex).Select ****'Tried to do it this way so
> choose correct book and sheet but dosen't work get error****
>
> With Selection.Interior
> .ColorIndex = 4
> .Pattern = xlSolid
> .PatternColorIndex = xlAutomatic
> End With
> End If
>
> rwindex = rwindex + 1
> Wend
>
> Can't understand why it is color coding workbook_name2 sheet
>
> Your expertise is appreciate. I think I have been look at the code to
> long and missing the obvious.
>
> Thanking you in advance.
>
> Pam
--
Dave Peterson
|
Similar Threads
