Changing tab color based on cell range colors

[lisaOT]

Can anyone tell me exactly how to do this? I want to make the tab color change to yellow if any of the cells in column B on that tab are highlighted yellow. There are a few tabs on this worksheet and I want to apply the same thing to each tab, where the tab only highlights yellow if something in Column B is highlighted yellow, and if there is nothing yellow on the page, then the tab has no color or is white. I've tried some VBA modules, though I really don't know how to write them, nor understand them, and I am lost. I can insert them and make them work, but I have no idea how to write one for this. Can anyone help with one please?

 

[lisaOT]

Can anyone tell me exactly how to do this? I want to make the tab color change to yellow if any of the cells in column B on that tab are highlighted yellow. There are a few tabs on this worksheet and I want to apply the same thing to each tab, where the tab only highlights yellow if something in Column B is highlighted yellow, and if there is nothing yellow on the page, then the tab has no color or is white. I've tried some VBA modules, though I really don't know how to write them, nor understand them, and I am lost. I can insert them and make them work, but I have no idea how to write one for this. Can anyone help with one please?

View attachment 76057

Start Up Order with check boxes unprotected with VBA.xlsm
	A	B	C	D	E			H	I
1		Computers
2		Item	Received		Missing			Comments
3		Desktop Computer			1
4		ChromeBooks			2
5
6
7
8
9
10
11
Computers

Cell Formulas
Range	Formula
E3	E3	=1-COUNTIF(F3,TRUE)
E4	E4	=2-COUNTIF(F4:G4,TRUE)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4	Expression	=$E$4>=1	text	NO
B3	Expression	=$E$3>=1	text	NO
E3:E4	Cell Value	>=1	text	NO

 

[lisaOT]

Start Up Order with check boxes unprotected with VBA.xlsm
	A	B	C	D	E			H	I
1		Computers
2		Item	Received		Missing			Comments
3		Desktop Computer			1
4		ChromeBooks			2
5
6
7
8
9
10
11
Computers

Cell Formulas
Range	Formula
E3	E3	=1-COUNTIF(F3,TRUE)
E4	E4	=2-COUNTIF(F4:G4,TRUE)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4	Expression	=$E$4>=1	text	NO
B3	Expression	=$E$3>=1	text	NO
E3:E4	Cell Value	>=1	text	NO

C3, C4 & D4 each have check boxes in them that don't show on the mini-sheet, there are hidden rows that produce the true/false if the boxes are checked or not.

 

[6StringJazzer]

First you should use one conditional formatting rule for all of column B, instead of one for each cell:

Applies to B:B

=$E1>=1

Same idea for column E.

Second, you should get rid of the merged cells in B1:H1. Use "Horizontal Center Across Selection"

Put this code into the module for this worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)

   If Not Intersect(Target, Range("E:E")) Is Nothing Then
   
      If Application.WorksheetFunction.CountIf(Range("E:E"), ">1") > 0 Then
         Me.Tab.Color = RGB(255, 255, 0)
      Else
         With Me.Tab
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
         End With
      End If
   
   End If

End Sub

 

[lisaOT]

First you should use one conditional formatting rule for all of column B, instead of one for each cell:

Applies to B:B

=$E1>=1

Same idea for column E.

Second, you should get rid of the merged cells in B1:H1. Use "Horizontal Center Across Selection"

Put this code into the module for this worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)

   If Not Intersect(Target, Range("E:E")) Is Nothing Then
  
      If Application.WorksheetFunction.CountIf(Range("E:E"), ">1") > 0 Then
         Me.Tab.Color = RGB(255, 255, 0)
      Else
         With Me.Tab
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
         End With
      End If
  
   End If

End Sub

Thank you so much for your help!
When I changed the conditional format to your suggestion, it also fills in the cells with the words in the column Headings (row 2), and I want those to stay white. I tried giving them a white fill before the formatting and after, and neither try worked and they still change yellow. I tried the code anyway, with keeping the conditional format the same way, and it does not work, but most likely because I could not get the conditional format to work the way you wanted it. Any ideas?

 

[lisaOT]

Start Up Order with check boxes unprotected.xlsx
	A	B	C	D	E			H	I
1		Computers
2		Item	Received		Missing			Comments
3		Desktop Computer			1
4		ChromeBooks			2
5
6
7
8
9
10
11
12
Computers

Cell Formulas
Range	Formula
E3	E3	=1-COUNTIF(F3,TRUE)
E4	E4	=2-COUNTIF(F4:G4,TRUE)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E3:E4	Cell Value	>=1	text	NO
B3:B4	Expression	=$E3>=1	text	NO

I've fixed a few things from the suggestions that @6StringJazzer made, and here is the updated sheet. A couple things did not work (see above response). Hoping someone can write a code for the module that might work for this one.