Database name 'tempdb' ignored, referencing object in tempdb.

This is not an error, it's only information message. It's caused by your code insert into tempdb..#BackupStatus.

You should use just insert into #BackupStatus (without specifying tempdb) because # already points to tempdb.

The error

Incorrect syntax near the keyword 'use'

is caused by

insert into tempdb..#BackupStatus
(Server_name , Full_Backup_Status_Weekly,Diff_Backup_Status_Daily,Transaction_log_backup_Hourly)
--select Server_name , Full_Backup_Status_Weekly,Diff_Backup_Status_Daily,Transaction_log_backup_Hourly from
values
use msdb
go

There are no values specified in values clause of your insert but instead there is use.

Just specify your values.

UPDATE

I reviewed your code and understood that you don't want to insert values but you want to insert the result of select from msdb using CTE, so I rewrote your code, it should look like this:

use msdb
go


IF OBJECT_ID('tempdb..#BackupStatus') IS NOT NULL 
DROP Table #BackupStatus

create table #BackupStatus(

Server_name VARCHAR(50),
Full_Backup_Status_Weekly VARCHAR(50),
Diff_Backup_Status_Daily VARCHAR(50),
Transaction_log_backup_Hourly VARCHAR(50));

--select * from #BackupStatus

WITH [MostRecentBackupStatus_CTE]
AS
(
    SELECT  bsfull.[server_name] ,
            bsfull.[database_name] ,
            bsfull.[backup_finish_date] AS [last_full_backup] ,
            bsdiff.[backup_finish_date] AS [last_diff_backup] ,
            bstlog.[backup_finish_date] AS [last_tran_backup] ,
            DATEDIFF(dd, bsfull.[backup_finish_date], CURRENT_TIMESTAMP) AS [days_since_full_backup] ,
            DATEDIFF(dd, bsdiff.[backup_finish_date], CURRENT_TIMESTAMP) AS [days_since_diff_backup] ,
            DATEDIFF(hh, bstlog.[backup_finish_date], CURRENT_TIMESTAMP) AS [hours_since_tranlog_backup] 

    FROM    [msdb]..[backupset] AS bsfull
            LEFT JOIN [msdb]..[backupset] AS bstlog ON bstlog.[database_name] = bsfull.[database_name]
                                                       AND bstlog.[server_name] = bsfull.[server_name]
                                                       AND bstlog.[type] = 'L'
                                                       AND bstlog.[backup_finish_date] = ( (SELECT  MAX([backup_finish_date])
                                                                                            FROM    [msdb]..[backupset] b2
                                                                                            WHERE   b2.[database_name] = bsfull.[database_name]
                                                                                                    AND b2.[server_name] = bsfull.[server_name]
                                                                                                    AND b2.[type] = 'L') )
            LEFT JOIN [msdb]..[backupset] AS bsdiff ON bsdiff.[database_name] = bsfull.[database_name]
                                                       AND bsdiff.[server_name] = bsfull.[server_name]
                                                       AND bsdiff.[type] = 'I'
                                                       AND bsdiff.[backup_finish_date] = ( (SELECT  MAX([backup_finish_date])
                                                                                            FROM    [msdb]..[backupset] b2
                                                                                            WHERE   b2.[database_name] = bsfull.[database_name]
                                                                                                    AND b2.[server_name] = bsfull.[server_name]
                                                                                                    AND b2.[type] = N'I') )
    WHERE   bsfull.[type] = N'D'
            AND bsfull.[backup_finish_date] = ( (SELECT MAX([backup_finish_date])
                                                 FROM   [msdb]..[backupset] b2
                                                 WHERE  b2.[database_name] = bsfull.[database_name]
                                                        AND b2.[server_name] = bsfull.[server_name]
                                                        AND b2.[type] = N'D') )
            AND EXISTS ( SELECT [name]
                         FROM   [master].[sys].[databases]
                         WHERE  [name] = bsfull.[database_name] )
            AND bsfull.[database_name] NOT IN  (N'tempdb' , N'Master' ,N'Model' , N'MSDB')
)


insert into #BackupStatus
(Server_name , Full_Backup_Status_Weekly,Diff_Backup_Status_Daily,Transaction_log_backup_Hourly)
SELECT  c.[server_name]  ,

       -- c.[database_name] ,
       -- d.[recovery_model_desc] ,
        --c.[last_full_backup] ,
        --c.[last_diff_backup] ,
        --c.[last_tran_backup] ,
        CASE 
            WHEN c.[days_since_full_backup] <= 7 THEN 'Success'
            WHEN c.[days_since_full_backup] >= 7 THEN 'Failed'
        ELSE NULL END AS Full_Backup_Status_Weekly,
            CASE 
            WHEN c.[days_since_diff_backup] <= 1 THEN 'Success'
            WHEN c.[days_since_diff_backup] >= 1 THEN 'Failed'
        ELSE NULL END AS Diff_Backup_Status_Daily,
        CASE 
            WHEN c.[hours_since_tranlog_backup] <= 1 THEN 'Success'
            WHEN c.[hours_since_tranlog_backup] >= 1 THEN 'Failed'
        ELSE NULL END AS Transaction_log_backup_Hourly


       --c.[days_since_full_backup] ,
       -- c.[days_since_diff_backup] ,
       -- c.[hours_since_tranlog_backup] 
        --c.[full_backup_location] ,
        --c.[diff_backup_location] ,
        --c.[tlog_backup_location]
FROM    [MostRecentBackupStatus_CTE] c
        INNER JOIN [master].[sys].[databases] d ON c.[database_name] = d.[name];

select * from #BackupStatus;

DROP Table #BackupStatus;