Comparing two books and two sheets, Problems with color coding correct sheet

[Pam]

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]

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().)

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