Conditional formatting tab colour based on cell range value

Hi all!

I should start out by clarifying, I have no programming experience with vba code. I require some assistance with a code for changing the colour of a sheet tab based on a particular number appearing in a range of cells.

I have attached an example to help with explaining what I am after.

The scenario is I have a cell range (C2:C16) that may or may not contain a value. If a cell in that range doesn't contain a value then no action is required, if it contains a value then I need to confirm if the corresponding cell in that row in range A2:A16 contains text or not. I have a formula I am using in range E2:E16 =IF(ISBLANK(C2),0,IF(AND(C2>0,A2<>""),1,2)) to return a 0,1,2 value to satisfy 3 different criteria. I then require a vba code that will change the colour of the sheet tab red if a "2" is returnerd in any cell in range E2:E16.

I tried using this vba code to no avail (it is in my excel sheet example)

Please Login or Register  to view this content.

ANy help with this would be greatly appreciated.

change the code to the following:
the color code for red is 3

Please Login or Register  to view this content.

brettosmith,
Change event should be controlled to restrict the range in order to prevent unnecessary execution.
This runs only if any one cell in A2:A16 or C2:C16 is changed, otherwise it ignores.

Please Login or Register  to view this content.

Hi jindon,

Thanks for the response.

If I was now to implement that into another "worksheet change" routine on that sheet, how would i go about it?

Below is my actual code that it needs to be implemented with:

Please Login or Register  to view this content.

Thanks jindon but I am getting a run-time error 1004, so when i chose to "debug" it highlights "Me.Tab.Color = vbRed"

See the attached.

It works fine on the test sheet but I am still getting a run-time error 1004 application-defined or object-defined error in my worksheet i require it for.

I am 99% sure it is because my workbook is protected and doesn't allow modifications to the structure - is there a way to allow this in the code?

Can not replicate the issue here...

It should work regardless of the workbook is protected or not.

jindon,

try the attached two examples. Both are protected workbooks with no password. Run-time error occurs whilst workbook is protected. It works fine if you unprotect workbook.

The "Template" Example is an actual worksheet from my workbook I am trying to get it working in. This is the best way to give you an idea of what I am working with.

Ok, then try add 2 lines

Please Login or Register  to view this content.

Thanks for your help jindon.

Much appreciated.