minimum values between two tables

[xplan303ex]

Hi all,
I am a total newbie in VBA. I tried to do this using Excel functions but I don't think it can be done, I tried writing my own VBA function but iI just don't know enough. Here's the scenario:

Workbook has two sheets with same sized tables with different values and sometimes text.

I want to create a table in a third sheet like this:

- Compare two tables cell by cell. If both cells contain text, print ***** in the new table.

- If one or both cells contain a number, print the minimum in the third table

(This is as far as I got with Excel functions)

Additionally, if this minimum number comes from sheet 1, give the cell a certain color, if it comes from sheet2, give it a different color. (I tried conditional formatting for this but I get an error that other worksheets can not be referenced for conditional formatting).

I can place the tables in the same locations if needed, but I prefer a function where I can select the ranges to be compared for the new table.

Any help will be GREATLY appreciated!

Thanks!

F

[antoka05]

See macreo 'Macro1' in attached file.

Macro compare values in sheets 'shhet1' and 'sheet2' and put the resul in sheet 'sheet3'.

I hope it can help you.

Regards,
Antonio

[xplan303ex]

Antonio, thanks so much!! This is very very helpful!

Since I have multiple tables on the same sheet, how can this macro be modified for the range to be a variable (meaning I select the range). I know I could just define range1, range2 and so on and apply the same code to each range, but selecting the range gives me more flexibility.

Thanks again!

[SOS]

Hi xplan303ex,

Perhaps adding the following to the code in the sample workbook might help

Please Login or Register  to view this content.

allowing you to type in the range that you wnat

[antoka05]

I modified the macro as you needed. Now you can simply select a range and run the macro: the macro will run only for selected cells.

Regards,
Antonio

[xplan303ex]

Thank you both! This is great help I appreciate it.