Sleeper: INDEX MATCH with a twist , not sure if possible in VBA

Hi enclosed is sheets , what i am trying to do is below.....

(Not sure if this is possible) :think:

MATCH COL D OF THIS SHEET, WITH COL G OF SHEET 2,.
IF SAME ROW HAS BLANK IN COL I AND A "REL" IN COL J IN SHEET 2, HIGHLIGHT ON HERE
BY COLOUR AND BOLD TEXT, AND GIVE USER OPORTUNITY TO DELETE ALL BUT THE MATCHED ROWS ON THIS SHEET…

ALSO IS THERE A WAY OF IF THE SHEET NAMES WERE SETUP LIKE THIS ("M50" AND "M55" AND "M74")

TO MAKE THE CODE RUN ON EACH SHEET APART FROM "KTAB" SHEET

Many Thanks

Merc

Quote:

---

Originally Posted by mercmannick

(Not sure if this is possible) :think:

---

The possible we do today, the impossible takes a little longer....

Code:

---

Sub Test_For_REL()
Dim r As Long
Dim vSheets() As Variant
Dim i As Integer

vSheets = Array("M50", "M55", "M55") ' add sheet names to match
    For i = LBound(vSheets) To UBound(vSheets) 'switch sheets automatically
        Sheets(vSheets(i)).Activate
            r = 2 ' the start row in the worksheet
        Do While Len(Range("A" & r).Formula) > 0
        ' repeat until first empty cell in column A
           
           
           
           
           
    Next i
   
End Sub

---

HeHe so true mdmackillop

This is as far as i got at moment not sure how to even start on remaining codes

Merc

I haven't time to look at the code immediately, but I've added some formula columns for you to look at. One approach might be to have your code insert the formulas, filter out the rows that don't match, and delete them. Then repeat for each sheet.

geekgirlau

thank you , unfortunately if i use formulas then when new sheet is pasted into sheet, it will overwrite the formulas, but maybe can Code to make formulas after pasted new sheet in

Merc

Yes, that's the general idea - have your code insert the formulas, then filter