Colour in cells where two criteria matches

[chrisellis250]

Hi all,

Bit of a toughy here, and not really sure how to go about it.

I have a spreadsheet of data on Sheet1, which is a table of data. Column M contains a URN and there will be a matching number to this, that links to a location in Sheet2.

If the number in Column M AND AE on Sheet1 (o2, 3, EE, Vodafone) matches the numbers AND the network in the table on Sheet 2, then cells M:T should be coloured by the colour of the location in Column B on the table in Sheet2.

The table in Sheet 2 is dynamic and will change, so could be A:J from A1 down to J100.

There will be number matches between Sheet1 and Sheet2, but where there is no network match - these are to stay uncoloured, it is only where both match.

The same then needs to be applied to column U, where the number needs to match the numbers in Sheet 2 and again AE needs to match the network in Sheet2.

Sheet1 in the attached file shows where numbers and the network in Sheet2 match they are coloured up.

Not sure how to go about it.

[mrice]

Try this macro.

Please Login or Register  to view this content.

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going the view ribbon in Excel

[chrisellis250]

Hi mrice,

Unbelievable, thank you very much. Almost there, it works perfectly for matching the number in Column M and network in AE, but only colours M, not M to T and then there is nothing coloured for any number and network matches in Column U and AE.

But it is brilliant. Thank you

[mrice]

Amended version to colour M - T

Please Login or Register  to view this content.