Running a macro over numerous sheets

The below code works great on one sheet, but how can I alter it so that it runs on Sheet1, Sheet2, Sheet3, Sheet4, Sheet5, Sheet6 and Sheet7. I know I could copy and paste the code 7 times and point it to the worksheets but I am sure there is an easier way?

Please Login or Register  to view this content.

Hello,
Like this, if the sheets are the first 7 in you workbook :

Please Login or Register  to view this content.

They are not the first 7 in my workbook though. They are scattered around between other sheets with names.

Ok then, use this method instead :
(name the sheets were indicated)

Please Login or Register  to view this content.

Brilliant.

This part is a bit more complex but only realising I need it now - How would I alter it so that it only color fills the word if column D contain one of the words AND column E contained another specified word.

So for example, if the set of words in column D were like the ones in the code, I would want it to fill the cell in color if it found one of those words in column D BUT also found a word that I specify in the column directly to the right of where the word was found.

So in my code word1 for column D is 'Alkan', but I'd only want Alkan to highlight in column D if column E on the same row contained the word 'America'

Can you be more precise...

Do you have multiple words possible in column E?
Are they different for each word in column D?

I want col D to highlight if col D contains one of the words listed above in the code but ONLY if col E contains another word I specify.

The word in col E would be specific to the word in Col D. So if col D contained 'Alkan' then I'd only want it to highlight if col E contained 'America'.

If you specify one word at a time then :

Please Login or Register  to view this content.

OK, so instead of the input box, if "MS" is found in col D then if col E contains 'Germany' then I want it to color. If 'Alkan' is found is col D then if col E contains 'Britain' I want it to color, and so on. Can this be done?

Can you add a tab in your workbook that would contain the data ?
For example in column A, you would have MS, Alkan, J2, ...
And in column B, you would have : Germany, Britain, ...

I''d rather not have the data on an actual tab in the workbook. Surely it's possible to just place the words in the code itself?

Yes, like this :
Make sure you have the same number of arguments in WordsD and WordsE arrays.
If you don't want to highlight the cells if column E is empty, replace "" by some dummy text in WordsE array.

Please Login or Register  to view this content.

I'll give that a go now. Just to confirm, in your code above, if col D is MS and E is Germany it will highlight, but if col D is MS and E is Britain it will NOT highlight?

Works perfectly. How do I change it so it highlights both Col D and E instead of just D and I will be finished!

Excellent !

Replace :

Please Login or Register  to view this content.

by

Please Login or Register  to view this content.

Thanks GC. Much appreciated.