A macro, in its simplest form, is a function of type syntax -> syntax, or, a function that takes syntax as input and produces syntax as output. These functions are invoked at "compile time" as the compiler is processing your source code, and the resulting code is then compiled or interpreted along with the rest of the code. The way you write these functions, what these functions are capable of doing, and even the precise data type of syntax varies between the languages that implement macros.
The most powerful macro systems allow you to "extend" your language with new syntactic constructs and features. In this sense you can think of macros as a way for you to team up with the compiler to improve the language. Macros also allow you to generate repetitive code that would be tedious or error-prone to hand-write and maintain on your own.
The easiest way to understand good macro systems would be to learn about them from a Lisp-variant like Clojure, Scheme, or Common Lisp, as macros have typically been the domain of these languages. It is still an active area of language design research to try to effectively integrate a good macro system into non-Lisp languages. Scala's macro proposal is supposedly heavily inspired by a language called Nemerle, which runs on the .NET platform. I don't know much about Nemerle, but it appears to be innovating very well in this particular space.
One of the designers of Dylan tried to push this in his new language called PLOT (Programming Language for Old Timers). It has pythonic syntax and provids a full powerd macro-system. Unfortently there is no implmentation of PLOT just a discription: http://users.rcn.com/david-moon/PLOT/
They work by not evaluating expressions but treating them as blocks of unevaluated code. The blocks of code can then be manipulated, transformed, split apart and reassembled all using the full power of the language. You aren't manipulating the values of those expressions, but the code itself.
The simplest example of a macro is a function which does not evaluate its arguments until you specifically tell it to do so. But they can be arbitrarily more complex than that.
Lisp is very flexible because it has no syntax to speak of and code is data. So manipulating the code is the same as manipulating data. With languages like Scala that have syntax, macros have to work with either strings representing the language elements or objects which represent them (expression trees as they are called in the post). In other words, they work with some abstract version of the syntax of the language.
Observe (aaa bbb ccc). If you just look at it as a datastructure, it's a list of 3 elements. If you look at it as executable code, then aaa is the operator, which operates on bbb and ccc.
If aaa is that special kind of operator which is a macro, it can rearrange (aaa bbb ccc) to be something different, which is then executed. Anything. It replaces itself with something else. If you programmed in a language like that, you'd never have to wait for some language designer to create a new for() loop. Because you could easily write a loop which is then transformed into the usual for(i=0;...) loop.
The evaluation strategy for Lisp functions is to evaluate each of the inputs before making the function call. In the example above, (* 4 5) is evaluated first before the call to the function + even begins; once you have the intermediate form (+ 3 20) where all the parameters are fully-evaluated, the function call happens.
Why are macros important? Let's say that you want to write a short-circuit AND. You can't do that as a function. For example, (and false (/ 1 0)) will return false (never evaluating the right-hand expression) if you have short-circuit AND but it will raise an error (division by zero) if you don't. If that were a function, that call would (undesirably) fail. So what this means is that you can't write the short-circuiting AND as a function. You need something else that operates on expression forms, not values. In practice, it's a macro whose behavior is approximately like so:
(and <X> <Y>) => (if <X> <Y> false)
This is a transformation of code that happens during compilation, not a run-time function call.
It's worth noting that IF is not a function or a macro. It's a "special form" provided by the language. The evaluation of if-forms is not like the evaluation of function forms; some branches are never evaluated. It requires special-case handling.
That's one use for macros: writing control-flow primitives. There's a lot more to them as well: code generation to eliminate boiler-plate, DSL creation. Common Lisp, for example, has an object system built on macros, and a (controversial) LOOP macro that is its own DSL for expressing iterations, allowing idioms like (loop for i from 4 downto 1 collect (* i i)), which would return (16 9 4 1).
Macros can be very useful. They're pretty much essential because of how Lisp works, so every Lisper ends up writing a few, often to substantial benefit. That said, macros can also be abused to make write-only code. You have to be careful with them, and the maxim, "Never use a macro when a function will do", is certainly worth heeding.