formatting cells

[ockie]

Hi,
seeking help on my following problem
I am trying to color a cell eg A1. if there is a value in B1, C1 etc A1 will turn yellow. but as soon as there is no value in a cell eg D1 A1 reverts to normal. In summary A1 will be highlighted as long as the is a value in B1:Z1
thanks robert

 

[kweaver]

How about this: select the A column and using this condition/formula for your conditional formatting:

=SUMPRODUCT(--(B1:Z1<>""))<>0

 

[Trebor76]

Hi Robert,

This can be done via Conditional Formatting. Try the following five steps:

1. Select A1 on the tab you want the fill to happen
2. Click the Home ribbon and from the Styles tab select Conditional Formatting > New Rule
3. From the New Formatting Rule dialog select "Use a formula to determine which cells to format" and in the "Format values where this formula is true" text box enter =COUNTA(B1:Z1)>0
4. Click Format and from the Format Cells dialog click the Fill (last) tab and select whatever colour you want cell A1 to be filled with is there's anything in B1:Z1
5. Click OK x 2

Test by putting some data in any cell in the range B1:Z1

Regards,

Robert

 

[ockie]

Hi kweaver,
thanks for your reply, this works until you leave a cell blank. My aim is to have A1 yellow when every cell in row A is filled. If a cell is missed then A1 reverts back to its normal colour.I will then copy down for the rows i wish this to be active on.

this is for a winter swimming club and this will be a quick way of seeing who has turned up every week.

 

[ockie]

Hi Trebor76,
this works until you leave a cell blank eg C1

 

[kweaver]

AH, when EVERY cell in the row is filled.

 

[Trebor76]

this works until you leave a cell blank eg C1

So if you want cell A1 to only change colour once every cell in B1:Z1 has something in it then change the formula in step 3 of my solution to this:

=COUNTA(B1:Z1)=25

 

[ockie]

Hi trebor,

I would like this to be progressive eg A1 is yellow when there is a number in B1 , then stays yellow when C1 is populated D1 etc, I would like A1 to revert to its normal colour when there is no data in a cell be that F1 J1 , I only want A1 to be yellow as the row fills up as soon as a cell is missed A1 reverts to normal colour.
I use this sheet for a sporting event, This is like an attendance sheet each cell is a week that someone has attended. A1 is yellow only when the row has 100% attendance.

 

[Trebor76]

I'm a little confused

Here you say...

I would like this to be progressive eg A1 is yellow when there is a number in B1 , then stays yellow when C1 is populated D1 etc

...but then here you say this:

A1 is yellow only when the row has 100% attendance

Which is it?

 

[ockie]

Hi Trebor,
I would like A1 to be yellow when something is put in B1 and stay yellow when there is something in C1 etc, as soon as there is a cell that contains no data A1 reverts back.
the 100% attendance.
the only row that A3 would be yellow would be row 3.
cell A1 would have been yellow until column E
row A2 would be yellow till row D

	A	B	C	D	E	E	G	H	I	J	K	L	M
row 1	3	2	1	4	5		1	1	2	3	4	5	6
row 2	2	1	4		4	4	3	1	2	3	4	5	6
row 3	1	2	3	4	5	6	5	4	3	2	1	4	1