mp_robinson_uk
12-04-2004, 04:42 PM
Hi All,
I would like to see if there is a better way of doing this.
The code does the following
I have a spreedsheet where on sheet one I have (number of columns can vary but this should not matter)
type start end title effect (first row is a header row)
set Mick Rob Mr yes
set Sam Rob Mr no
set John Lee Mr yes
set Dave Jac Mr 1
set Mick Kil Mr yes
set Bill Dav Mr 2
second page has just the start and end names
Mick Kil
John Lee
whenever the names match between the second and first page I would like the relivant row in the first page to have a green background i.e. match page2 column1 = page1 column2 and page2 column2 = page1 column3
Mick Kil matches row 6 so the row with "set Mick Kil Mr yes" would have a green background and
John Lee matches row 4 so the row with "set Mick Kil Mr yes" would have a green background
I did the following, it works but what could I have done better ;-)
Thanks for your help - great forum!
Option Explicit
Sub Add_Col_Row()
Dim LastRow1 As Long
Dim LastRow2 As Long
Dim Counter1 As Integer
Dim Counter2 As Integer
LastRow1 = Sheets("Sheet1").Cells.Find(What:="*", LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
LastRow2 = Sheets("Sheet2").Cells.Find(What:="*", LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
Counter1 = LastRow1 - 1
Counter2 = LastRow2 - 1
Do While Counter2 > 0
Do While Counter1 > 1
If Sheets("Sheet2").Cells(Counter2, 1).Value = _
Sheets("Sheet1").Cells(Counter1, 2).Value Then
If Sheets("Sheet2").Cells(Counter2, 2).Value = _
Sheets("Sheet1").Cells(Counter1, 3).Value Then
Sheets("Sheet1").Rows(Counter1 & ":" & Counter1).Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
End If
End If
Counter1 = Counter1 - 1
Loop
Counter1 = LastRow1 - 1
Counter2 = Counter2 - 1
Loop
End Sub
I would like to see if there is a better way of doing this.
The code does the following
I have a spreedsheet where on sheet one I have (number of columns can vary but this should not matter)
type start end title effect (first row is a header row)
set Mick Rob Mr yes
set Sam Rob Mr no
set John Lee Mr yes
set Dave Jac Mr 1
set Mick Kil Mr yes
set Bill Dav Mr 2
second page has just the start and end names
Mick Kil
John Lee
whenever the names match between the second and first page I would like the relivant row in the first page to have a green background i.e. match page2 column1 = page1 column2 and page2 column2 = page1 column3
Mick Kil matches row 6 so the row with "set Mick Kil Mr yes" would have a green background and
John Lee matches row 4 so the row with "set Mick Kil Mr yes" would have a green background
I did the following, it works but what could I have done better ;-)
Thanks for your help - great forum!
Option Explicit
Sub Add_Col_Row()
Dim LastRow1 As Long
Dim LastRow2 As Long
Dim Counter1 As Integer
Dim Counter2 As Integer
LastRow1 = Sheets("Sheet1").Cells.Find(What:="*", LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
LastRow2 = Sheets("Sheet2").Cells.Find(What:="*", LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
Counter1 = LastRow1 - 1
Counter2 = LastRow2 - 1
Do While Counter2 > 0
Do While Counter1 > 1
If Sheets("Sheet2").Cells(Counter2, 1).Value = _
Sheets("Sheet1").Cells(Counter1, 2).Value Then
If Sheets("Sheet2").Cells(Counter2, 2).Value = _
Sheets("Sheet1").Cells(Counter1, 3).Value Then
Sheets("Sheet1").Rows(Counter1 & ":" & Counter1).Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
End If
End If
Counter1 = Counter1 - 1
Loop
Counter1 = LastRow1 - 1
Counter2 = Counter2 - 1
Loop
End Sub