Private Hosted Zone pointing to public ALB

Adding to the extended discussion, the reason you can't connect to any of the public IPs that appear to be inside your VPC is that they aren't actually inside the VPC. Instead, traffic to a public IP address is translated when, and only when, a packet sent to one passes through an Internet Gateway (IGW) attached to the VPC hosting the elastic network interface (ENI) to which the public IP address is attached.

For inbound traffic from the internet (including traffic from other AWS resources in the same region reaching out to your IGW through their own IGWs), incoming connections have their destination IP translated to the primary private IP address of the ENI to which the public IP belongs.

For outbound traffic, connections to the other side of the IGW have the source IPs belonging to the ENI to which the public IP belongs translated to the public IP.

This is documented in this article: https://docs.aws.amazon.com/vpc/latest/userguide/vpc-igw-internet-access.html#ip-addresses-and-nat

Traffic inside a VPC doesn't pass through its attached IGW in either direction. Routing traffic through a NAT gateway is possible, but it only translates the source IP address of the connection, not the public IP that is the destination. There are complex workarounds, such as using multiple VPCs and possibly attaching some of the EC2 instances to both VPCs, but they tend to get complicated and may involve overlapping costs.

Usually, the simplest solution is to set up a separate internal ALB inside the same VPC and configure it otherwise identically. Then create a private hosted zone (PHZ) in Route 53 for the public name (not the entire parent domain, only the public hostname of the ALB) and attach it to the VPC. Create an alias A record with an empty name (because the zone name is the same as the ALB's name) and point it to the internal ALB. This way, when the name of the ALB is queried from inside the VPC, the attached PHZ will return the private IPs of the internal ALB and not the public one.

If having a second ALB is problematic, perhaps because of an automation or IaC construct that only supports associating target resources with one ALB target group at a time, another alternative is to create an internal-only Network Load Balancer (NLB) that uses the target group type of ALB to point to the public ALB. The principle with the PHZ is the same, now just pointed to the internal NLB instead of a second ALB. The immediate benefit is that the ALB-level configuration of listener rules, certificates, logging, etc. is only configured once on the public ALB, and the NLB only passes through the raw TCP connections from internal users to the ALB, without knowing or caring about any specifics, such as TLS certificates or URL paths. In some cases, some of the other added features of an NLB may provide added value, such as the NLB's IP addresses remaining static, as opposed to an ALB's IPs changing at arbitrary intervals, and the NLB being possible to publish as a VPC endpoint service to share with other VPCs and accounts.

Whether the second load balancer is an ALB or NLB, the PHZ will have no effect on name resolution for DNS queries from outside the VPC with which the PHZ is associated. External users will continue to have the DNS name resolve to the public IPs of the existing ALB.

Note that for the public ALB, if you're using a public Route 53 hosted zone for your public DNS, you shouldn't use a CNAME record. You should instead create an A type (for IPv4) or AAAA (for IPv6) alias record and configure the destination as the DNS name of the ALB in the appropriate region. The difference is that Route 53 will not charge you for queries for alias records pointing to AWS resources, while the intermediate CNAME queries are chargeable. The additional CNAME query will also incur an additional round-trip by users connecting to the ALB, reducing performance. Finally, the CNAME will expose the native, immutable name of your ALB, which will allow malicious or suspect actors on the internet to track your resources by their native IDs, while an alias record only returns the constantly changing, ephemeral public IPs of an ALB that looks like every other.

Alias records for Route 53 are discussed in more detail in this documentation article: https://docs.aws.amazon.com/Route53/latest/DeveloperGuide/resource-record-sets-choosing-alias-non-alias.html