Compare two colums to eachother

  • Hi all again


    Quick one (I hope).... If i have two columns, what would be the best way to compare all the values in these two columns and if any values exist in both columns, albeit anywhere in these columns, then the cells in both columns are changed to green, whereas any cells that dont exist in both columns are changes red? Any suggestions? I can't think how to go about this.


    Many Thanks in advance


    Matt

  • Re: Compare two colums to eachother


    Hi matt boy,


    Name the range of cells used in your first column as (say) rng1 and your second column rng2.


    Highlight all the used cells in column 1 and click:
    Format > Conditional Formatting...
    Condition1 > (drop down) Formula Is


    and enter


    =ISERROR(MATCH(A1,rng2,0))=FALSE


    click Format and set the colour, then OK.


    Repeat for the cells in column 2, changing the range name to rng1.


    Hope this helps.


    Regards,
    Batman.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: Compare two colums to eachother


    Hi Matt Boy


    VBA Solution


    Set your range as needed






    End Sub

    [FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
    Tip: To avoid chasing code always use Option Explicit.

  • Re: Compare two colums to eachother


    orry matt boy i mis read your request


    try this modification to my previous post


    [FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
    Tip: To avoid chasing code always use Option Explicit.

  • Re: Compare two colums to eachother


    Hi Gents


    Thanks ever so much for the suggestions!! Tried both methods and couldnt get the conditional formatting to work (not too sure what i was doing wrong but I dont doubt it was me getting it wrong!) however the code worked a treat with no changes. Apologies if my question was none too clear originally. Really appreciate the help though.


    Thanks Bill and Batman!!!!!


    Matt

  • Re: Compare two colums to eachother


    Help needed again!!!!


    The colums I'm using are on 2 different sheets and for some reason I can't seem to get this to work. Sorry if I'm being stupid, I am trying!! Here is what I've tried to do on top of Bills code.


    It keeps coming up with runtime error 1004
    Unable to set the colourindex property of the interior class


    Please could someone let me know what I'm doing wrong and explain it to me, it's doing my head in :yikes:


    Many many thanks


    Matt


    By the way if anyone could let me know how to improve on my code, I know it properly isn't written in the best way, then please let me know. I know there are meant to be certain methods which are deemed as good practice however i don't know what these are (I'm only a beginner to VB). Thanks

  • Re: Compare two colums to eachother


    Hi matt boy,


    Try to avoid Activate and Select to manipulate objects, and use Workbooks instead of Windows. In your example:


    Code
    Set rngIRd = Workbooks("Invoice Check.xls").Worksheets("invoices recieved").Range("A3:A10")     
        Set rngACs = Workbooks("Invoice Check.xls").Worksheets("All Cons").Range("A3:A10")
         
        ' Rest of your code here


    should work (it worked OK for me).


    By the way, the code does not set a background colour in the second range if there is no match to the first. The quickest way to do this would be to set the background to the second range to all red before the loop.


    If this is the extent of the program, I would be tempted to persist with the Conditional Formatting route, but if this is part of a larger program the VBA code route may be the better option.


    Hope this helps.


    Regards,
    Batman.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: Compare two colums to eachother


    Hi Batman


    I tried using the code you said but I still coundn't get it to work :?


    So then I tried the Conditional Formatting and was getting that to work however when I tried to use other worksheets (like i was doing in the coded version) excel told me I may not use references from other worksheets or workbooks for conditional formatting which blows that option out of the water (which is kinda unforunate because a lot easier), even though it allows you to input reference to other workheets. So now Im back to my original problem. Is there any way you could post what you said you got to work?


    This is what I did with your code, sorry if you think im being stupid!!



    Just gettin the same error as before


    Thanks v much


    Matt

  • Re: Compare two colums to eachother


    Hi Matt,


    If you don't display file extensions in Windows (i.e. the file name at the top of the screen when activated doesn't show .xls at the end), remove the ".xls" from the workbook names.


    I will post again shortly when I have created an example using Conditional Formatting.


    Regards,
    Batman.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: Compare two colums to eachother


    Hi Matt,


    The workbook attached gives you an example of using conditional formatting to solve your problem. Points to note:


    1) You need to name the ranges of cells to compare to get this functionality to work between worksheets. You can either select the range of cells to use, click on the Names box drop down to the left of the Formula Bar and enter your name, or you can use Dynamic Named Ranges.


    I have opted for the latter as it avoids the need to name a range bigger than you currently want, or having to extend the range each time you add a new entry. You can't use the method described above to create a range name in this way; you need to go to Insert > Name > Define... and enter a range name and a formula for the range. You won't see the range names in the Names field either. Take a look at the ranges I have created (rng1 and rng2). You can also search this forum to see previous examples of using these ranges.


    2) To set up the conditional formatting for the first range of cells, highlight the range to format, go to Format > Conditional Formatting... and enter the formulas for those cells. The cell reference you need to use is that of the first cell in the range you have selected. When you have created the first conditional format, click Add and add a second format with the second formula. See my example for the formulas to use. Repeat the process on the second set of cells.


    Let me know if you are still having problems.


    Regards,
    Batman.

  • Re: Compare two colums to eachother


    Hi again


    removing the file extensions worked and the code will run now!! Happy days


    Thanks for the help batman however it would be mostly appreciated if you could still post the example using conditional formatting as I may decide to use this instead and it will help my knoledge of excel. I can get it to work on the same sheet however referencing another sheet seems to cause problems.


    All your help is greatly appreciated


    Matt


    Just got your example as i posted this will let you know if i get any other problems


    Once again thanks v much

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!