It is known that an abelian category is equivalent to a module category iff it has a finite projective generator and contains arbitary direct sum of that generator by Theorem 1 of Chapter 4 Section 11 of the book "Categories and Functors" by Bodo Pareigis.
We know that arbitary comodule category over a coalgebra is an abelian category, it is natural to ask the following question: when a comodule category is equivalent to a module category?
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.
The general answer (without the compatibility with respect to fibre functors as in the answer of Todd) to this question was given in Vercruysse, Joost Equivalences between categories of modules and categories of comodules. Acta Math. Sin. (Engl. Ser.) 24 (2008), no. 10, 1655–1674. see also https://arxiv.org/abs/math/0604423
The idea is similar to Morita equivalence for module categories, and makes use of the theory so-called "Galois comodules", which is in fact an instance of comonadicity or descent.
