Highlighting a Column Header Based off a Condition in the Cells in the Column Below being met

[ahotz01]

Is there a conditional formatting formula that would allow you to highlight a column header (A1) if a condition is met in the cells below in that entire column?

Condition : All cells containing the value of 1 are highlighted green.

Notes:
- Not all cells/rows in that column will contain the value 1
- Only those cells containing the value of 1 will be highlighted green, BUT, they will be highlighted green at different times (when they are complete)
- Once all cells in that column, containing 1, are highlighted green, then cell A1 (header) would need to turn green to reflect that column is complete

 

[etaf]

how do you decide when its complete ?

countif(cell range , 1 ) = number expected to complete ???

 

[ahotz01]

how do you decide when its complete ?

countif(cell range , 1 ) = number expected to complete ???

It is being prepopulated with 1 to show that it applies to the # listed in column A. once complete (that will be determined manually outside the sheet, it will be highlighted green. Once we have manually highlighted all the 1's in a column green, I'm wanting the column header (CL.xx) to be highlighted green

	CL.xx
a	1​
b
c	1​
d	1​
e	1​
f
g
h	1​
i	1​

 

[etaf]

in your example
is that complete - even though not all the rows have a 1 in

counta(a2:a1000) = Sum(B2:b1000)

Book5
	A	B
1		CL.xx
2	a	1
3	b	1
4	c	1
5	d	1
6	e	1
7	f	1
8	g	1
9	h	1
10	i	1
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1	Expression	=COUNTA($A$2:$A$1000)=SUM($B$2:$B$1000)	text	NO
B2:B10	Expression	=B2=1	text	NO

 

[ahotz01]

in your example
is that complete - even though not all the rows have a 1 in

counta(a2:a1000) = Sum(B2:b1000)

Book5
	A	B
1		CL.xx
2	a	1
3	b	1
4	c	1
5	d	1
6	e	1
7	f	1
8	g	1
9	h	1
10	i	1
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1	Expression	=COUNTA($A$2:$A$1000)=SUM($B$2:$B$1000)	text	NO
B2:B10	Expression	=B2=1	text	NO

not every row will have a 1 in it. The 1 will be input from the beginning and highlighted green once complete. Again, once all the 1's are highlighted within that column, the header for that column would also turn green to signify complete/all the 1s for that column have been highlighted green/completed. A better example might be something like the following.

	CL.34	CL.75	CL.3
a	1​
b		1​
c	1​	1​
d	1​		1​
e	1​	1​
f		1​
g
h	1​
i	1​	1​

 

[etaf]

i think you may need VBA - so something fills the cells with 1 Green only when that column is complete

so because cell in D5 is now green fill - then that column is complete and should change the header

are the cells with 1 manually filled green when deemed complete

Book6
	A	B	C	D
1		CL.34	CL.75	CL.3
2	a	1
3	b		1
4	c	1	1
5	d	1		1
6	e	1	1
7	f		1
8	g
9	h	1
10	i	1	1
Sheet1

 

[ahotz01]

i think you may need VBA - so something fills the cells with 1 Green only when that column is complete

so because cell in D5 is now green fill - then that column is complete and should change the header

are the cells with 1 manually filled green when deemed complete

Book6
	A	B	C	D
1		CL.34	CL.75	CL.3
2	a	1
3	b		1
4	c	1	1
5	d	1		1
6	e	1	1
7	f		1
8	g
9	h	1
10	i	1	1
Sheet1

"so because cell in D5 is now green fill - then that column is complete and should change the header" - yes, change the header (cell D1 in this example) green because all cells in that column with a 1 (manually populated) have also been manually populated green).

"are the cells with 1 manually filled green when deemed complete" - yes

 

[etaf]

ok, sorry - you will need VBA to test a cell for the fill colour
conditional formatting cannot do that yet

I do not provide VBA solutions on the forum, sorry