formatting cells

ockie

New Member
Joined
Jan 11, 2020
Messages
11
Office Version
2013
Platform
Windows
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
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

kweaver

Well-known Member
Joined
May 8, 2018
Messages
995
Office Version
365, 2010
How about this: select the A column and using this condition/formula for your conditional formatting:
Code:
=SUMPRODUCT(--(B1:Z1<>""))<>0
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,587
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

New Member
Joined
Jan 11, 2020
Messages
11
Office Version
2013
Platform
Windows
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

New Member
Joined
Jan 11, 2020
Messages
11
Office Version
2013
Platform
Windows
Hi Trebor76,
this works until you leave a cell blank eg C1
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
995
Office Version
365, 2010
AH, when EVERY cell in the row is filled.
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,587
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

New Member
Joined
Jan 11, 2020
Messages
11
Office Version
2013
Platform
Windows
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

Well-known Member
Joined
Jul 23, 2007
Messages
4,587
I'm a little confused :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

New Member
Joined
Jan 11, 2020
Messages
11
Office Version
2013
Platform
Windows
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
ABCDEEGHIJKLM
row 1321451123456
row 2214443123456
row 31234565432141
 

Watch MrExcel Video

Forum statistics

Threads
1,095,732
Messages
5,446,184
Members
405,390
Latest member
RafalKowalski

This Week's Hot Topics

Top