While his abilities are never completely spelled out (part of the magic stylistically common in Studio Ghibli productions), No-Face is a being driven strongly by positive reinforcement and the approval of others. It is this that defines his name- he changes who he is dependent on the aspects of his environment, and has no real identity of his own.
In examining the musical score made by Joe Hisaishi and the New Japan Philharmonic Orchestra, the track titled "No-Face" is suspenseful, loud, vibrant, and chaotic. This is used to emphasize No-Face's chameleon-like nature. He adapts to the experiences around him, taking cues from the denizens of the bathhouse, from Chihiro, from the Water Spirit, and of course, from Yubaba.
When the gold produced by No-Face turns into dust, No-Face has just come to the realization that the gold, while pleasing to most of the greedy bathhouse residents, is not pleasing to Chihiro. She, being the only one to show him kindness, has been the object of his attention from the time they met, and his goal has been to offer her his kindness in return. If the gold does not please her, it serves no purpose any longer being gold, and so it turns to dust.
In an interview with Toshio Suzuki (one of the producers of Spirited Away) in "The Art of Spirited Away" (a 'Making Of' featurette), he noted that the character of No-Face may have been based on aspects of the director Hayao Miyazaki.
"No-Face is desperate to enter somehow into the hearts of others, but really doesn't have the ways or means to do so."
This would seem to suggest that the gold is simply an illusion, based on what No-Face thinks will earn him the most acceptance from others.
