hey
i need a code wich will change the collour of a cell when the user enter the
word "off".
so when he enter "off" in c5 i need c5 to turn bright yelow..
(will this go in the worksheet change?)

On Apr 28, 5:24 pm, pswanie <pswa...@discussions.microsoft.com> wrote:
> hey
> i need a code wich will change the collour of a cell when the user enter the
> word "off".
> so when he enter "off" in c5 i need c5 to turn bright yelow..
> (will this go in the worksheet change?)

Try Conditional Formatting.

Go to Format -> Conditional Formatting

If cell value is equal to off, then click "Format...", go to the
Patterns tab and change the color to a bright yellow.

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

On Apr 28, 6:07 pm, pswanie <pswa...@discussions.microsoft.com> 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.