mysheet.Tab.Color=myrange.Interior.Color

Can you use if statements in that?
Such as, if cell O7 = "Plate" color  = Blue if cell O7 = "Rod" color = Green,
etc.

Sure, but use them in a select case statement

Sub ChangeTabColors()
Dim MySheet As Worksheet
Set MySheet = ActiveSheet
Select Case MySheet.Range("O7").Value

Case "Plate"
MySheet.Tab.Color = RGB(0, 0, 255)
Case "Rod"
MySheet.Tab.Color = RGB(0, 255, 0)
Case Else
MySheet.Tab.Color = RGB(127, 127, 127)
End Select
End Sub

Note:
RGB Colors:
Color: Red Value, Green Value, Blue Value
Black: 0, 0, 0
Blue: 0, 0, 255
Green: 0, 255, 0
Cyan: 0, 255, 255
Red: 255, 0, 0
Magenta: 255, 0, 255
Yellow: 255, 255, 0
White: 255, 255, 255

Grey: 127, 127, 127