Compare two columns in workbooks referencing partial names

[VBAmature31]

Hi all,

I am working on some of my first VBA tasks and am a little stuck on how to write code to suit the final piece.

I basically have two files:

File1 = SAP RMA 14th Aug
File 2 =Oracle RMA 14th Aug

The first parts of each filename will always be the same but the dates at the end will change(i.e. "14th Aug".)

I need to check that all delivery numbers in in column "A" in file 1 are contained somewhere in Column "G" in file2 and if they are found it will highlight the row green,If they are not found to highlight the row red.

I have been searching the forum and can't find anything that would do the above,

So far my VBA skills have got me as far as I can go and I am hoping you guys could help me on the last part.

Thanks,

 

[Deepak]

Can we have sample xl file pls.

 

[VBAmature31]

Hi,

Apologies,find both files attached

 

[Deepak]

Check this Simple CF while second wb has been copied in first wb.

Or you are looking to independently CF should to work having two different file (BY VBA).

 

[VBAmature31]

Hi Deepak,

No what you have done there would work absolutely fine,what was the code you used to achieve it?

Thanks so much for your help

 

[Deepak]

There' no code!! It's only CF.

=MATCH($A1*1,cpo,0)




where cpo is a named range.

 

[VBAmature31]

Hey Deepak,

Thanks for your reply,

This would work great is there a way to incorporate that into a VBA code?

thanks,

 

[Deepak]

Hey Deepak,

Thanks for your reply,

This would work great is there a way to incorporate that into a VBA code?

thanks,

Workbook will be one as i did or two separate??

 

[VBAmature31]

Hi Deepak,

The workbooks can be one like you did if you can make that work,

Thanks

 

[Deepak]

Hi,

Use simply this =COUNTIF(cpo,$A1)

or VBA

Sub CF_1()
    With Sheet1.Range("A1").CurrentRegion
        .FormatConditions.Delete
        .Select
        With .FormatConditions.Add(xlExpression, Formula1:="=COUNTIF(cpo,$A1)")
            .Interior.Color = 5287936
        End With
       
'    .FormatConditions(.FormatConditions.Count).SetFirstPriority
'  .FormatConditions(1).StopIfTrue = False
    End With
End Sub

 

[VBAmature31]

Hi Deepak,

That will work just perfect,

Thanks so much for your help.

regards

 

[Deepak]

Welcome Buddy!!