I mean the last Resident Evil that actually want to sleep with the lights on on something if ever. People talk about the formula of RE all the time but was the game still scary/creepy for you by Nemisis and Code Veronica?
None of them actually "scared" me, unless you count the occasional jump scare. Personally, the momentary surprise of a dog or licker jumping through a window isn't really a "scare," but more of a "startle."
As far as eerie atmosphere and dark stories go, the Remake, CVX, and the first third of RE4 (village) are my choices.