Check values on different sheets

[mp_robinson_uk]

Hi All,

I need some help creating a macro please. I have not got a clue how to do this.

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

column1 column2

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

Thanks,

Mick

[mp_robinson_uk]

Hi,

I should have also said the number of rows does change.

Thanks,

Mick

[Zack Barresse]

Hi,

Rows don't matter. You can use conditional formatting for this. Which would be easier to maintain (imho) and faster. There is a problem when attempting conditional formatting with a formula looking at other sheets. To bypass this 'flaw', use Named Ranges.

What I did was, on Sheet2, select all of column A, click on the name box (or Insert --> Name --> Define) and labeled it "myStart" (no quotes). I did the same thing with sheet2 column B and named it "myEnd". These named ranges we can use in conditional formatting.

So on Sheet1, select columns A:E ensuring that A1 is the Active cell! You can ensure this if you select the columns by clicking the column headers (clicking on the 'A' and dragging to the 'E'). Then go to Format --> Conditional Formatting --> Formula Is ...

=(A1<>"")*(ROW()<>1)*(ISNUMBER(MATCH($B1,myStart,0)))*(ISNUMBER(MATCH($C1,myEnd,0)))

Works great for me. Just ensure you set your format to that of desired. And remember, conditional formatting is 'technically' not the same as a traditional cell format. When testing with VBA, it will not give you the same results, most noticably seen in the Interior ColorIndex values of cells vs. Conditional Formatting ColorIndex values.


HTH

[mp_robinson_uk]

Hi Firefly,

It didn't work for me when I tried it I got an error message.

Anyway, I got the following to work so I will close this.

Thanks for you help

Mick

-----------------

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

[Zack Barresse]

Hi Mick,

If you would like and you upload the file, I can put the Conditional Formatting on it for you. It would take much less overhead and be much more efficient that any code will be. Plus it is in 'real time'.