I have resolved the issue of the conditional formatting by modifying the target.

Regarding the solution for the trim and carriage returns' removal, which work very efficiently, I am still running into a problem. My worksheet contains several other macros; one of the macros is to append the worked data to a different worksheet, and I am getting at the column "A" of the worksheet where I am applying your solution multiple rows with "#VALUE!" which when doing the append, it gives me incorrect results due to the contents of column "A".

I am wondering if possible to have your solution, instead of hard coded for a range, to have it dynamically to be triggered by an input to a cell of the column "A", and within the range "A4:A1004"

Thank you!