Changin colour of cell to meet criterai 1

[Cpreston]

HI

I have a spreadsheet like this

Free[pre] WO[pre] Alloc[pre] Week[pre] Total
1[pre] [pre]2[pre] [pre]3[pre] 5[pre] [pre]6[pre]
1[pre] [pre]1[pre] [pre]1[pre] 3[pre] [pre]3[pre]
3[pre] [pre]3[pre] [pre]2[pre] 9[pre] [pre]8[pre]

I want the column Week to highlight in some way (perhaps cell fills in red) if the Total is less than Week. I have tried in conditional formatting but cannot get it to work. (The total is Free + WO + Allo columns)

I would like it to only highlight the Week column also that as a value in it and not fill the entire column full of red cells.

I have tried to set a conditional format with Use a formula to determine cells to format and have this in the Format rules where this formula is true part
="if($D$2:$D$4<$E$2:$E$4)"

I have the Preview for Red and it to apply to =$d:$d and the stop if true unticked.

This is not working and does not apply anything to column D which is the week column

Any ideas what I am doing wrong and how I can fix please.

Thanks

 

[Andrzejek]

Is this how your data looks like:

[pre]
Free WO Alloc Week Total
1 2 3 5 6
1 1 1 3 3
3 3 2 9 8[/pre]

Please use "Preview" before posting.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Cpreston]

Hi

I did preview how but assumed the pre would have gone after posting, how did you get it to look how it should (Yes your example is how it should look)

Thanks

 

[Andrzejek]

Type your data, highlight the text you want to align, click on Pre tag

You can also do this ‘by hand’:[tt][ignore]
[pre]
Your data to be align here
[/pre][/ignore][/tt]


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Cpreston]

Ok great, thanks will make sure I do that in the future.

Hopefully someone can help with the problem with Excel now

 

[SkipVought]

Hi,

What you're looking for is Conditional Format. It is a built-in feature. Chech HELP. If you are adding rows of data, your CF will work better if your table is a Structured Table.

1) Select the data range that you want the format to affect.

2) Open the CF wizard.

In most cases I use a formula. Your formula will always be with respect to the upper left-hand cell of the selection in 1).

Your formula, assuming that data starts in A2:
[tt]
=$D2>$E2
[/tt]

 

[Cpreston]

Hi

Sorry still not working.

Rule Applied (In conditional formatting rules manager)is showing this

Rule (applied in order shown) Formula: ="$D4<$E4" (I need it for any figure in D and E column)
Format is Red
Applies to =$1:$65536 (I selected the whole sheet)

Thanks

 

[SkipVought]

So you have Excel 97-2003?

Please explain exactly what you did, step by step.

 

[Cpreston]

Excel 2010
Highlighted the entire sheet
Went to conditional formatting
New Rule
Use a formlua to determine which cells to format
Put the ="$D4<$E4" in the values where the formula is true
selected red from preview
Clicked OK

 

[SkipVought]

Well no wonder it does not work!

You totally ignored the steps I outlined!

By the way, your sheet has way more rows than 65,536, which was exceeded with Excel 2007 and following versions.

 

[Cpreston]

Hi
Sorry
Ok just worked through your steps again and I have a result, but not quite what I wanted but very close

I highlighted just the data that was there and then did the conditional formatting

Went to conditional formatting
New Rule
Use a formula to determine which cells to format
Put the =$D2>$E2" in the values where the formula is true
selected red from preview
Clicked OK
This Applies to =$A$2:$F$4.

This highlights the entire row in red that is true. However, can I get it so it just fills the cell in, which in this case would be the Week column, where it is true.
Also the spread sheet could grow so how can I get it so the conditional format grows as the rows do? I have just added a row which the total is less than the week but it does not carry the conditional format over.

Thanks

 

[SkipVought]

1) select the data range that you want the format to affect. That would be the data in column D. Yes?

Use the Structured Table feature. Insert > Tables > Table to convert your table to a ST. Do some research in Excel HELP to understand the power of the feature. VERY VERY POWERFUL!

 

[Cpreston]

Hi

Selected the data range for column D and also converted to a table all working now

Many thanks