On Apr 28, 6:07 pm, pswanie wrote:
thats how and what they do now. but i want to automate the colour change...

and i forgot. any word (open, close, off) must if possible be in caps. if
caps aint on it must change the word to caps

I obtained the code in the body by simply recording a macro and doing
the conditional formatting. The code will change the color of a cell
to yellow if the user inputs the word "off". The code will sit in a
Worksheet_Change event sub:

Private Sub Worksheet_Change(ByVal Target As Range)

Target.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""off"""
Target.FormatConditions(1).Interior.ColorIndex = 6

End Sub

I'm not sure how to do the caps part, best wait for a reply from the
more experienced users on that one.