Change tab color if any cell fits a conditional format

muhleebbin

Board Regular
Joined
Sep 30, 2017
Messages
181
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
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!
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,842
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
I don't think you can test for a changing of a cells color.
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,855
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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:
Excel Formula:
=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:
VBA Code:
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.
 
Solution

muhleebbin

Board Regular
Joined
Sep 30, 2017
Messages
181
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
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!!
 

Forum statistics

Threads
1,147,823
Messages
5,743,406
Members
423,792
Latest member
travisds

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top