Need Help Creating a VBScript or Macro to create a column & fill out with data from others

I've been looking all over for a solution online with no luck, hoping you can help me. I have a the following spreadsheet:

Column A
Header: Work Location City
Redwood City
Sacramento
Visalia
Woodland Hills
Atlanta
Miami
Louisville
St. Louis

Column B
Header: Work Location State
Redwood City
Sacramento
Visalia
Woodland Hills
Atlanta
Miami
Louisville
St. Louis

Column C
Header: Region
Northwest
Northwest
Southwest
Southwest
South
Florida
Central
Missouri

I'm trying to create a script that automates column C. For every state other than California, it needs to add the name of the region listed there (Ex. Georgia is on Region South, Florida is on Region Florida, Kentucky is on Central, Missouri is on Missouri) For California it gets trickier, it needs to look at column A and based on the city it's either going to be on Northwest or Southwest.

So basically the script would first create a column next to the Work Location State, then create a header for it called "Region", then fill it out accordingly (this is just an example, the spreadsheet will have 20,000+rows and other states)

Hoping you can help me. Unfortunately this is above my excel knowledge level and I've hit a wall. Thanks!!