Change tab color if any cell fits a conditional format

[muhleebbin]

Hi,

I have the following conditional format where it compares two sheets and highlights any differences:

=A1<>'Test Sheet'!A1 for range A1:Z1000

It highlights any cell in yellow.

Is there a way where if any cell (or multiple cells) are highlighted that it changes the tab color to yellow as well?

Thank you in advance for your assistance!

 

[johnnyL]

I don't think you can test for a changing of a cells color.

 

[bobsan42]

well, you should not check the color. certainly not make any check cell by cell. you will need something fast.
this works for me on 64 bit O365:
I put this formula in cell AB1:

=SUM(N(A1:Z1000<>'test sheet'!A1:Z1000))

it counts the number of different cell values in the ranges. The only problem is that empty cell equals 0 or an empty string. I don't know if this will present a problem in your case.

then I include a line of code in the relevant sheet code module:

Private Sub Worksheet_Calculate()
    If Me.Range("AB1").Value > 0 Then Me.Tab.Color = vbYellow Else Me.Tab.ColorIndex = xlNone
End Sub

Since I don't know all the details I am not sure if the Change event will not be a better option. But the calculate event will probably do the job
I cannot immediately test this on other office versions at the moment.

 

[muhleebbin]

Thanks bobsan42!! This actually works for me!

I just have my code adding this formula into cell AB1 so the worksheet_calculate does work for my use case.

Thanks again for your idea here!!