Constraints based on other columns
First of all, Welcome to dba.stackexchange.com and thanks for your post !!

Is it possible to limit what values are allowed in a column based off of other values in the row.

Yes using CHECK CONSTRAINTS as described here

Example :

create table myTable (ID int identity(1,1)
                        , Test_mode int
                        , Active int 
                        )
go

-- Active can only be NULL, 1, 0, AND only 1 with Test_mode as 0.
ALTER TABLE myTable WITH CHECK ADD 
   CONSTRAINT ck_active CHECK (active IS NULL OR active IN (1, 0)) 
   go

-- some test data
insert into myTable (test_mode, Active) values (1, null)
insert into myTable (test_mode, Active) values (0, null)
insert into myTable (test_mode, Active) values (1, 0)
insert into myTable (test_mode, Active) values (0, 1)
insert into myTable (test_mode, Active) values (1, 1)

select * from myTable

-- Is there a way to either change the value of Test_mode to 0 if a 1 is inserted into Active

update myTable
set Test_mode = case when Active = 1 then  0
        else Test_mode 
        end
where Active = 1


If Test_mode is 1 not allow insertion/update of Active --OR--
Throw some kind of error if Test_mode is 1 and an insertion/update of Active is attempted.

Use TRY/CATCH as described here