Matching and finding data then colouring the font depending on result found

[Eurekaonide]

Ok I wonder if you gurus can help me (again!) Please.

I have one workbook in v 2007 two tabs, one is "Data_Import" the other is "Data-Output"

On Data-Import I have headers in the top row;

Ref, Name, Gate1, Gate2, Gate3 etc
1234, test, 01/05/2014, 01/06/2014,01/07/2014 etc
5678,test2, 02/05/2014, 05/07/2014,01/08/2014 etc

on the date-Output tab I have headers in the top row;
ref, Name, (dates in each column from 01/01/2014 - 31/12/2014)

I have a VLookup formula currently in the name column looking up from the manually input ref, referring back to the Data_inport tab.

What I then want to do is in each corresponding cell along each row in the Data_output tab to look at the date for that column in say C1 and look to see if this date matches the date in the correct row within the Data_inport tab for that ref in Gate1, Gate2, Gate3 etc and then put a "s" if a match is found.

I can currently do this fine by using the below formula;

=IF(OR(C$1=Data_import!$K49,C$1=Data_import!$L49,C$1=Data_import!$M49,C$1=Data_import!$N49,C$1=Data_import!$O49,C$1=Data_import!$P49,C$1=Data_import!$Q49,C$1=Data_import!$R49,C$1=Data_import!$S49,C$1=Data_import!$T49,C$1=Data_import!$U49,C$1=Data_import!$V49,C$1=Data_import!$W49,C$1=Data_import!$X49,C$1=Data_import!$Y49,C$1=Data_import!$Z49,),"s","")

However, what I would like to do is;
1) Place this into a VBA code so its quicker and easier to run
2) Depending on which column in the Data-Import tab returns a positive match I want to change the font colour of the "s" in the Data_Output tab.

I was thinking of doing it via a Case scenario but do not seem to be able to get it to lookup all the different values across the different pages.

I guess in summary its;
Data_Output cell C2 = does the ref in A2 match a ref in data_Import tab column A - Yes then does the date in the Data_Output tab C1 match a date in Data_Import Column C (Row ref=same row as matched "Ref") - yes colour the font red (in the dataOutput tab) next cell along - if No - does it match the date in Column D in Data_Import tab - no does it match date in column E etc etc.

Your help is much appreciated.

 

[Eurekaonide]

Dear OK,

I have got this far; its just a placement of a formula in one cell which if matches the date/data it then colours the cell font and changes the number format to the letter "s".

However because it just keeps overwriting the cell with the next formula it looses the outcome on any previous matches.

Therefore can anyone smarten this up so that it holds onto what ever is a true result? Not fussed at the moment if this is the first instance of true or the last.

' PM1 Plan Date
    ActiveCell.Formula = "=IF(TI$2=Data_import!$K57,1,0)"
    With Selection
             .Font.Color = vbWhite
    End With
   If ActiveCell.Value = 1 Then ActiveCell.Value = "s"
   
  ' PM2 Plan Date
       ActiveCell.Formula = "=IF(TI$2=Data_import!$M57,2,0)"
    With Selection
        .Font.Color = vbYellow
    End With
        
   If ActiveCell.Value = 2 Then ActiveCell.Value = "s"
    ' PM3 Plan Date
       ActiveCell.Formula = "=IF(TI$2=Data_import!$O57,3,0)"
    With Selection
        .Font.Color = vbRed
    End With
        
   If ActiveCell.Value = 3 Then ActiveCell.Value = "s"
    ' PM4 Plan Date
       ActiveCell.Formula = "=IF(TI$2=Data_import!$Q57,4,0)"
    With Selection
        .Font.Color = vbGreen
    End With
        
   If ActiveCell.Value = 4 Then ActiveCell.Value = "s"
    
      ' PM5 Plan Date
       ActiveCell.Formula = "=IF(TI$2=Data_import!$S57,5,0)"
    With Selection
        .Font.Color = -6736897
    End With
        
   If ActiveCell.Value = 5 Then ActiveCell.Value = "s"
    
       ' PM6 Plan Date
       ActiveCell.Formula = "=IF(TI$2=Data_import!$U57,6,0)"
    With Selection.Font
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = -0.499984740745262
    End With
        
   If ActiveCell.Value = 6 Then ActiveCell.Value = "s"
    
   ' PM7 Plan Date
       ActiveCell.Formula = "=IF(TI$2=Data_import!$W57,7,0)"
    With Selection.Font
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = -0.249977111117893
    End With
        
   If ActiveCell.Value = 7 Then ActiveCell.Value = "s"
   ' PM8 Plan Date
       ActiveCell.Formula = "=IF(TI$2=Data_import!$Y57,8,0)"
    With Selection
        .Font.Color = vbBlack
        
    End With
        
   If ActiveCell.Value = 8 Then ActiveCell.Value = "s"

 

[Eurekaonide]

Dear All

I am still ahving this issue, I have now tried this formula but its not quite right can you help me fix it please?

=IF(MATCH($K2,Data_import!$M54,0),1,0)+IF(MATCH($K2,Data_import!$N54,0),1,0)+IF(MATCH($K2,Data_import!$O54,0),1,0)

I'm trying to see if I can match the date in K2 to the date in the Data_import sheet cell M54 if it matches I want to display a "1" in the active cell (say G20) if it does not match I then want to see if K2 date matches the date in Data_import sheet N54 and then display a 2 in the active cell (G20) and again in O54 etc etc