While Steam generally focuses on multiplayer cheating, they typically don't care too much on single player stuff.
HOWEVER, any game that utilizes VAC (like Valve's Source Engine Games) will take cheating in any form & mode seriously, which hacking the game or utilizing external cheats may trigger. VAC bans are usually automatic (as VAC detects the outside modification of the games) & are permanent with no chance of an appeal. While getting a VAC ban doesn't ban you from Steam services completely, it does negatively affect your account in multiple ways.
1) You're permanently banned from all VAC-enabled game servers.
2) Items & Games that are VAC-enabled are locked to the account & cannot be gifted, traded or otherwised moved from the banned account.
3) You won't be able to participate in communications or provide community content (like guides) in VAC-enabled games.
Games that have both single player & multiplayer support (like GTA V & GTA Online) will not allow cheating in single player mode if the effects extended into multiplayer mode. However, these games will likely employ their own cheat detection systems for this.