No, you can't change the JOIN tables with a CASE statement.
However, you can change JOIN criteria with a CASE statement.
In your case, I would LEFT JOIN the two tables and use CASE statement in the SELECT part.
SELECT s.*, CASE WHEN s.zType = 'out' then t1.ColValue ELSE t2.ColINT END
FROM Source AS s
LEFT JOIN Table1 AS t1 ON t1.Col1 = s.ColX
LEFT JOIN Table2 AS t2 ON t2.Col1 = s.ColY