How can add custom text to a cell based on a specific entry in a different cell?

[ChemistV700]

My laboratory is capable of running 20+ different analyses, and we get contracts from about the same 15 companies to do a combination of these analyses. I created an Excel spread sheet to keep track of the work as it comes in, where columns are the 20 different analyses we can run, and rows are the companies. I type in either a checkmark or "NA", depending on whether that company requests that specific analysis. (Each company requests its own combination of analyses).
I need some help with the following:
If I enter "Company 1" in cell A100, I want cell B100 to display "NA". If I enter "Company 2" instead, I want cell D100 to display "NA". And if I enter "Company 3", do nothing, for example. I am OK with adding the check marks manually, as there are other variables that need not be mentioned.
Now, I have been able to develop some toy solution in VBA to some extent (please see code below). However, I have two issues:

1. In order to run the code, I have to switch to the VBA editor and press F5 after every entry. Instead, I would like it to work like when using formulas for the cells. In other words, if I type in "Company 1" in any cell of column A and hit "Enter", I would like the "NA" to display automatically in the appropriate cells on the row. I guess I could record a macro for this, but the file is shared with many people and I would prefer to avoid that.
2. In the future I will need to add more companies and analyses, so I need a code I can quickly go in and update. Or maybe have a list of companies that I add to and link it somehow to my code.
   

   Sub writeNA()
   For i = 1 To 20 Step 1
   
   x = Cells(i, 1).Value
   
   If x = "Company 1" Then
   Cells(i, 2).Value = "NA"
   End If
   
   If x = "Company 2" Then
   Cells(i, 3).Value = "NA"
   End If
   
   If x = "Company 3" Then
   Cells(i, 4).Value = "NA"
   End If
   
   Next
   End Sub

Thank you!

 

[My Aswer Is This]

So you would like a script that would automatically run when you enter a value in column A
Is that correct.
That can be done automatically when you enter a value in column A

But when you say Company1 and company 2 and company 3

The script would have to know each company name
How can the script know that?

Like if "Apple" is entered in A3 how would a script know what column to put NA in?
Since the company names are entered in first column of each row
There is nothing in Range(G1) for example to tell the script to put NA here.

 

[ChemistV700]

So you would like a script that would automatically run when you enter a value in column A
Is that correct.
That can be done automatically when you enter a value in column A

But when you say Company1 and company 2 and company 3

The script would have to know each company name
How can the script know that?

Like if "Apple" is entered in A3 how would a script know what column to put NA in?
Since the company names are entered in first column of each row
There is nothing in Range(G1) for example to tell the script to put NA here.

Thank you for your response. Sorry if I wasn't clear before, but the individual companies always request the same analyses. So I am thinking of having a list of what analyses Companies 1, 2, 3... request. Then if Company 1 requests analyses A, B, C, I want nothing there (I will put checkmarks manually) but I want the script to put NA's for all the other analyses.

 

[My Aswer Is This]

Thank you for your response. Sorry if I wasn't clear before, but the individual companies always request the same analyses. So I am thinking of having a list of what analyses Companies 1, 2, 3... request. Then if Company 1 requests analyses A, B, C, I want nothing there (I will put checkmarks manually) but I want the script to put NA's for all the other analyses.

Well if you plan to manually enter a check mark in the column if the company wants that analyses.
I could just have the script automatically enter NA in any cell that is empty in that row. Correct
But tell me how do you plan to put a checkmark in the cell. What font and what character do you use to enter a check mark I do not have a checkmark on my keyboard? Or I just tell the script to enter NA in all empty cells in that row as long as there is text in Row1 which is where the headers are.

 

[ChemistV700]

That would mean putting in the check marks first, which would not work, because I end up adding them after the analysis is complete. The spreadsheet is more of a running "live" list of projects and companies we work with.
But I was able to find a solution by having a separate table (on another sheet) that establishes a pattern, then pulling that up when entering a company.

Thank you very much for taking a look at my question.