ActiveWorkbook.Sheets("TEST").Tab.ColorIndex = 3

[newuser08]

Not use where to start.

Firstly can I say I ‘m a completed newb to excel vba so please go easy on me.

In sheet1 I have a validation cell (A1 “Validation”) when True=1 else blank.

I’m wanting to change the sheet tab colour to… err Red if Validation=1.

Any suggestions… please try to keep them simple ish.

Thanks

 

[Chance1234]

Well, do you want this to happen on the cell change , or when the user opens a workbook ? or other way

eitehr way , you are going need to put your code in that event.

You are going to need a test condition as you described above,

you are then going to need the code to change your sheet tab colour. Have you recorded a macro and looked at the VBA ?

Chance,

F, G + 2MSTG

 

[newuser08]

I would like in on a 'on open event'
I did record a macro and tried:


'##########################################

Private Sub Workbook_Open()

If Sheet10("Validation", 1) Then

Sheets("TEST").Select
ActiveWorkbook.Sheets("TEST").Tab.ColorIndex = 3

Else

Sheets("TEST").Select
ActiveWorkbook.Sheets("TEST").Tab.ColorIndex = 1

End If
End Sub

As you can imagine it fell over.

Suggestions??

 

[Chance1234]

If Sheet1.Range("valid").Value = 1 Then

    Sheet1.Tab.ColorIndex = 3
Else
    Sheet1.Tab.ColorIndex = 1
End If

Chance,

F, G + 2MSTG

 

[newuser08]

Worked a treat many thanks.

 

[newuser08]

Is it possible to make the above work in a shared excel book?