I have found the answer elsewhere in the community after digging around a bit more.
And here is an article that explains what is going on:
The problem we had is that when Cloudflare was caching the site (using the orange cloud with straight arrow), it resulted in too many redirects 301 and the site was unreachable.
Having turned off caching (using the grey cloud with arrow over the top), the original IP address is returned and everything works correctly.
It’s not immediately obvious that Cloudflare will change the IP address if you select the orange cloud (and caching), and IMO, this should be made clearer in the UI to avoid having find and read documentation explaining it. Especially for someone that might not know how to determine a different IP address is being returned by Cloudflare compared to the one you actually create.
No idea why it resulted in 301 redirect and the site being unreachable but I’m happy in this instance to not take advantage of Cloudflare caching.