Compare values and place variable value on sh2
 

In the following code, I am not understanding how to pass the variable value
to my other sheet (Sh2).

===============
'====================
'Originating author: Joel from "microsoft.public.excel.programming" 10.09.07
'Modified 10.10.07
'====================
'compare sheet 1 with sheet 2
For Each sh1cell In sh1range
Set c = sh2range.Find( _
What:=sh1cell, LookIn:=xlValues, LookAt:=xlWhole)' Added "xlWhole"
If c Is Nothing Then
sh1cell.Offset(0, 2).Value = "No Match Found!"
sh1cell.Offset(0, 2).Font.Color = -16776961
sh1cell.Offset(0, 2).Font.Bold = True
Else
If sh1cell.Offset(0, 1) < c.Offset(0, 1) Then
sh1cell.Offset(0, 2).Font.Italic = True
sh1cell.Offset(0, 2).ColumnWidth = 25
sh1cell.Offset(0, 2).Value = "Revision Level Change!"
sSh1RevIs = sh1cell.Offset(0, 1) ' Capture Revision level of Sh1 if
False
MsgBox sSh1RevIs 'for testing...Show Rev from Sh1
End If
End If
Next sh1cell
====================
The variable "sSh1RevIs" captures the value I want (revision level of
"sh1cell.Offset(0, 1)") but I cannot figure out how to place this value on
the same row (column D) on Sh2 (compared row of Sh1 and Sh2).

--
Regards

Rick
XP Pro
Office 2007

Compare values and place variable value on sh2
 

sheets("Sheet2").Range("D" & sh1cell.row) = sSh1RevIs


"Rick S." wrote:

In the following code, I am not understanding how to pass the variable value
to my other sheet (Sh2).

===============
'====================
'Originating author: Joel from "microsoft.public.excel.programming" 10.09.07
'Modified 10.10.07
'====================
'compare sheet 1 with sheet 2
For Each sh1cell In sh1range
Set c = sh2range.Find( _
What:=sh1cell, LookIn:=xlValues, LookAt:=xlWhole)' Added "xlWhole"
If c Is Nothing Then
sh1cell.Offset(0, 2).Value = "No Match Found!"
sh1cell.Offset(0, 2).Font.Color = -16776961
sh1cell.Offset(0, 2).Font.Bold = True
Else
If sh1cell.Offset(0, 1) < c.Offset(0, 1) Then
sh1cell.Offset(0, 2).Font.Italic = True
sh1cell.Offset(0, 2).ColumnWidth = 25
sh1cell.Offset(0, 2).Value = "Revision Level Change!"
sSh1RevIs = sh1cell.Offset(0, 1) ' Capture Revision level of Sh1 if
False
MsgBox sSh1RevIs 'for testing...Show Rev from Sh1
End If
End If
Next sh1cell
====================
The variable "sSh1RevIs" captures the value I want (revision level of
"sh1cell.Offset(0, 1)") but I cannot figure out how to place this value on
the same row (column D) on Sh2 (compared row of Sh1 and Sh2).

--
Regards

Rick
XP Pro
Office 2007

Compare values and place variable value on sh2
 

Once again, Thank You!

--
Regards

Rick
XP Pro
Office 2007



"Joel" wrote:

sheets("Sheet2").Range("D" & sh1cell.row) = sSh1RevIs


"Rick S." wrote:

In the following code, I am not understanding how to pass the variable value
to my other sheet (Sh2).

===============
'====================
'Originating author: Joel from "microsoft.public.excel.programming" 10.09.07
'Modified 10.10.07
'====================
'compare sheet 1 with sheet 2
For Each sh1cell In sh1range
Set c = sh2range.Find( _
What:=sh1cell, LookIn:=xlValues, LookAt:=xlWhole)' Added "xlWhole"
If c Is Nothing Then
sh1cell.Offset(0, 2).Value = "No Match Found!"
sh1cell.Offset(0, 2).Font.Color = -16776961
sh1cell.Offset(0, 2).Font.Bold = True
Else
If sh1cell.Offset(0, 1) < c.Offset(0, 1) Then
sh1cell.Offset(0, 2).Font.Italic = True
sh1cell.Offset(0, 2).ColumnWidth = 25
sh1cell.Offset(0, 2).Value = "Revision Level Change!"
sSh1RevIs = sh1cell.Offset(0, 1) ' Capture Revision level of Sh1 if
False
MsgBox sSh1RevIs 'for testing...Show Rev from Sh1
End If
End If
Next sh1cell
====================
The variable "sSh1RevIs" captures the value I want (revision level of
"sh1cell.Offset(0, 1)") but I cannot figure out how to place this value on
the same row (column D) on Sh2 (compared row of Sh1 and Sh2).

--
Regards

Rick
XP Pro
Office 2007