I think a lot of it depends on what ‘aspect’ of their person features most strongly in their work.

To pick a specific example: Ronald Dahl was a virulent anti-semite. And yet he was also clearly someone with a significant degree of empathy for children, after he himself suffered abuse at boarding school. His works come from this angle, seeking to provide children with the catharsis of seeing retributive justice done to evil adults. He also shows class conscience in books like “Danny, the champion of the world”.

I would argue that it is the ideas conveyed in a work that make it worthy, or unworthy, of consumption, rather than the author themselves.

As an aside, you may be interested in the works of the writer/philosopher Iris Murdoch, who does have something to say on this topic, around ‘can bad people produce good art’.