First, an easy sufficient condition. Let $C$ be a coalgebra over a commutative ring $R$. If $C$ itself is finitely generated and projective as an $R$-module, then $Comod_C$ is equivalent to the category of modules $Mod_{C^\ast}$ over the dual algebra $C^\ast = Mod_R(C, R)$.

First, for $C$ a finite projective module, we have a natural isomorphism

$$C^\ast \otimes_R - \cong Mod_R(C, -): Mod_R \to Mod_R$$

because finite projectivity of $C$ is equivalent to the condition that the hom-functor $Mod_R(C, -)$ preserves colimits, and any colimit preserving endofunctor $F: Mod_R \to Mod_R$ is necessarily isomorphic to $F(R) \otimes_R -$. It follows that $C \otimes_R -$ is left adjoint to $C^\ast \otimes_R -$; by exploiting the symmetry of the tensor product, it is also *right* adjoint to $C^\ast \otimes_R -$.

Next, the coalgebra structure on $C$ endows the functor $C \otimes_R -$ with a comonad structure, so that the category of $C$-comodules, $Comod_C$, is equivalent to the category of coalgebras of the comonad $C \otimes_R -$. It is a general fact (and not hard to see for oneself) that for a comonad $G$ that possesses a left adjoint $F$, the left adjoint has an induced monad structure and the category of $F$-algebras is equivalent to the category of $G$-coalgebras. (This result was given in one of the first papers on general monad theory, the famous 1965 paper by Eilenberg-Moore. I am pretty sure it's given in the book by Mac Lane-Moerdijk on topos theory, but I don't have this to hand at the moment.) It follows that $Comod_C$ is equivalent to the category of algebras over the monad $C^\ast \otimes_R -$, in other words the category $Mod_{C^\ast}$.

This can also be turned around. Suppose that we have an equivalence $Comod_C \simeq Mod_A$ which is compatible with the evident underlying or forgetful functors to $Mod_R$. This means that the underlying functor

$$U: Comod_C \to Mod_R,$$

which has a *right* adjoint $Mod_R \to Comod_C$ (something that is true for the underlying functor on a category of coalgebras over any comonad), also has a left adjoint

$$A \otimes_R -: Mod_R \to Mod_A \simeq Comod_C.$$

Thus, the comonad $C \otimes_R -: Mod_R \to Mod_R$ has a left adjoint, say $F$. This left adjoint preserves colimits, and so as we said a little while ago, it must be of the form $B \otimes_R -$ where $B = F(R)$. (In fact $B \cong A$, but we don't need this.) Since $C \otimes_R -$ and $Mod_R(B, -)$ are both right adjoint to $B \otimes_R -$, we deduce an equivalence

$$C \otimes_R - \cong Mod_R(B, -)$$

and we are in the same situation we had before, where $Mod_R(B, -)$ is cocontinuous, so $B$ is finitely generated and projective, and its dual module $C = B^\ast$ is also finitely generated and projective.

So if you add the assumption that the equivalence between the category of $C$-comodules and the category of modules respects the underlying functors to $Mod_R$, then this is equivalent to $C$ being finitely generated and projective. I don't know off the bat what one could say if this compatibility assumption is removed.

4more comments