Use the current space factor code. I also load amsthm because
\documentclass{article}
% not needed if amsthm is loaded
\def\frenchspacing{\sfcode`\.1006\sfcode`\?1005\sfcode`\!1004%
\sfcode`\:1003\sfcode`\;1002\sfcode`\,1001 }
%%%
\makeatletter
\newcommand{\afterbigpunctornot}{%
\ifnum\spacefactor>\sfcode`:
\expandafter\@firstoftwo
\else
\expandafter\@secondoftwo
\fi
}
\makeatother
\newcommand{\great}{\afterbigpunctornot{G}{g}reat}
\begin{document}
This is it. \great! Wait, what is \great?
This is it! \great! Wait, what is \great?
This is it? \great! Wait, what is \great?
This is it, \great! Wait, what is \great?
This is it; \great! Wait, what is \great?
This is it: \great! Wait, what is \great?
\frenchspacing
This is it. \great! Wait, what is \great?
This is it! \great! Wait, what is \great?
This is it? \great! Wait, what is \great?
This is it, \great! Wait, what is \great?
This is it; \great! Wait, what is \great?
This is it: \great! Wait, what is \great?
\end{document}

The idea is that the colon has the highest space factor code among all non “big” punctuation marks. We have to redefine \frenchspacing because the default definition just sets all space factor codes to 1000.
If you need the change only after the exclamation mark, we have to set its space factor code to a unique one.
\documentclass{article}
% not needed if amsthm is loaded
\sfcode`!=\numexpr\sfcode`!+1\relax
\def\frenchspacing{\sfcode`\.1006\sfcode`\?1005\sfcode`\!1004%
\sfcode`\:1003\sfcode`\;1002\sfcode`\,1001 }
%%%
\makeatletter
\newcommand{\afterexclamation}{%
\ifnum\spacefactor=\sfcode`!
\expandafter\@firstoftwo
\else
\expandafter\@secondoftwo
\fi
}
\makeatother
\newcommand{\great}{\afterexclamation{Grrrr}{g}reat}
\begin{document}
This is it! \great! Wait, what is \great?
This is it. \great! Wait, what is \great?
This is it? \great! Wait, what is \great?
This is it, \great! Wait, what is \great?
This is it; \great! Wait, what is \great?
This is it: \great! Wait, what is \great?
\frenchspacing
This is it! \great! Wait, what is \great?
This is it. \great! Wait, what is \great?
This is it? \great! Wait, what is \great?
This is it, \great! Wait, what is \great?
This is it; \great! Wait, what is \great?
This is it: \great! Wait, what is \great?
\end{document}

\MakeUppercaseafter a period. – nox Jul 12 '18 at 12:26