I'm in the process of trying to decide which language is better for me - Common Lisp and Scheme. One of the crucial points to take into account is macros, since I personally consider macros to be the most salient feature of Lisp which sets it apart from other languages.
The choice is far from simple, of course. On one hand, CL's macros are conceptually much simpler than Scheme's. While defmacro is pure CL code with the difference that it gets executed at compile-time, Scheme's syntax-rules is a specialized sub-language which is almost-but-not-quite Scheme. Therefore, it loses the convenient equivalence to normal Scheme code and is, at least for me, much harder to understand and much less intuitive to use.
On the other hand, CL's defmacro suffers from the lack of macro hygiene, as opposed to Scheme's macro system.
Simple examples of this symptom are easy to fix. Consider this macro (from Practical Common Lisp) - iterating over prime numbers in a given range:
However, there are cases that can't be fixed in any simple way. For example, look at the last code sample again - what if do-primes is called inside some context where do is redefined to be a different function (with flet or labels). Sure, this is a far fetched example - but it can happen, and the code will surely break. Think about it - the macro can be used successfully for years, until it gets use in a slightly weird piece of code - and then it will fail in a mysterious way which is quite difficult to debug.
Another far-fetched-but-can-happen problem: if a macro refers to some global symbol and is called inside a lexical context that shadows this symbol. Another hard-to-fix problem.
There are probably other examples of where defmacro can break. Sure, with a judicious use of gensyms and solid coding conventions the probability of something happening is low, and indeed, tons of CL code with macros work perfectly. However, there's this nagging feeling of a looming disaster that may strike at some unknown time with a slightly non-standard piece of code. This isn't pleasant.
Scheme's macro system is hygienic and doesn't suffer from these problems. Of course, this comes at the price of simplicity and ease-of-use, as I mentioned earlier.
P.S. Interestingly, you can write code with defmacro in Scheme if you want. Most of the major implementations support it. It appears that Scheme's macros can be used to implement (non-hygienic) CL macros, but not the other way around.