Excel 2010 Conditional Formating - Format all cells according to other cells values 1

[Triacona]

Excel 2010 Conditional Formating - Format all cells according to other cells values

Dear All,

Thanks for a great forum!

I am trying to coditionally format a selection of cells according to values in corresponding cells.

So on the selection

 =$A$2:$D$117

I have this formula

 =D2:D117="NA"

And again

Selection

 =$A$2:$D$117

I have this formula

 =D2:D117="YES"

The problem comes in where the only cell that is formated is the A column.

So if the value changes in D2 to D117 the only cells that are formatted are A2:A117...yet I want A2117 to format.

So Row by Row when the user enters YES or NA I want it to format the line respectively, so A22.

This used to work in Excel 2003, but now does not seem to work?

Is there a way to format an entire block instead of formatting each line?

Any help will be greatly appreciated.

Thanks


Thank you,

Kind regards

Triacona

 

[SkipVought]

Hi,

Here's how to do CF:

1 select the cells that you want formatted

2 open the CF Wizard

3 ALL formulas are with respect to the TOP ROW

So...
If the "NA" & "YES" occur in column D and you want the entire row to contain the format then...
[tt]
=$D2="NA"
[/tt]
Is your first formula, meaning no matter what column A format that range for each row for the value in that row.



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Triacona]

Dear Skip,

Thanks for your reply

I have followed 1 and 2.
On three I have done as you have instructed, just selected $D2 = "NA" and then a separate format for $D2 = "YES".
So that does it for all rows.
Thank you very much

Just so I understand better, why isn't the formula =D2117 = "NA" and =D2117 = "YES" ? As in my mind this is the whole range.

When I followed instruction 3 excel selected the cell as $D$2, so I changed it to just $D2, is that significant?

I am self trained, and these explainations would help.

Thank you again for your help

Thank you,

Kind regards

Triacona

 

[SkipVought]

Self taught is good! That means that you are accustomed to dig for knowledge.

"Just so I understand better, why isn't the formula =D2117 = "NA" and =D2117 = "YES" ?"

ALL formulas are entered with respect to the TOP ROW of the selection. The range of the CF is implied in the SELECTION. You could also choose to only select a single row for CF and enter the formula with respect to that row, Then you would need to COPY the CF range and Edit > Paste Special FORMATS for any other ranges.

"When I followed instruction 3 excel selected the cell as $D$2, so I changed it to just $D2, is that significant?"

[highlight #73D216]In Excel HELP look up The difference between absolute, relative and mixed references.[/highlight] You know that when you copy a formula down the ROW number will increment with each row and when you copy a formula across, the column letter will increment also. Sometimes you don't want that to happen, like in your D2="NA" formula. If you select A22 and then enter the CF formula, D2="NA", what would happen is the formula in A22 would be A2="NA", B2="NA", C2="NA", D2="NA". But you want the CF formula to ALL refer to D2!, hence the CF formula $D2="NA" meaning this is a Mixed reference holding column D ABSOLUTE and row 2 RELATIVE, so when I copy this to another row, it will ALL point to column D in whatever row.





Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Triacona]

Dear Skip,

That's wonderful thank you

It is much appreciated and I will also look the "

The difference between absolute, relative and mixed references"

up.

Thanks again!

Thank you,

Kind regards

Triacona