The TV serial Sherlock presents Sherlock and Mycroft as rather snarky atheists.
From The Sign of Three:
SHERLOCK: ... and contrast is, after all, God’s own plan to enhance the beauty of his creation ... or it would be if God were not a ludicrous fantasy designed to provide a career opportunity for the family idiot.
From The Final Problem:
MYCROFT: Heaven may be a fantasy for the credulous and the afraid. But I can give you a map reference for hell.
By contrast Conan Doyle's Holmes rarely comments on religious matters, and when he does he seems to express a kind of reverent agnosticism.
From The Complete Adventures of Sherlock Holmes:
SHERLOCK: Life is infinitely stranger than anything which the mind of man could invent. We would not dare to conceive the things which are really mere commonplaces of existence. If we could fly out of that window hand in hand, hover over this great city, gently remove the roofs, and and peep in at the queer things which are going on, the strange coincidences, the plannings, the cross-purposes, the wonderful chains of events, working through generations, and leading to the most outre results, it would make all fiction with its conventionalities and foreseen conclusions most stale and unprofitable.
And from The Memoirs of Sherlock Holmes:
SHERLOCK: But this rose is an extra. Its smell and its color are an embellishment of life, not a condition of it. It is only goodness which gives extras, and so I say again that we have much to hope from the flowers.
Why then this development in Sherlock?