P
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
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