Conditional Formatting Colours Wrong Cell

[excelnoob94]

Hello,

I am having issues with conditional formatting and cells.

I currently have a document with 630 rows and 19 columns (varying between text and numbers)

I am trying to do conditional formatting based off a each individual cell in column 'S' for each row which will conditional format that row i.e. cell S615 has the value 7 which is above the conditional formatting rule of if >0.5 row 615 turns red.

An issue I am finding is when I put the value of '7' in S615 the row above i.e. row 614 or even several spaces above i.e. row 611 will turn red, not row 615. Any suggestions how I can fix this?

Also how should my conditional formatting rule look? currently it is =$S5>0.5 turn red and applies to all the cells in the workbook.

I can try and clarify if you have questions

Appreciate the assistance

 

[jtakw]

Hi,

That's pretty simple and straight forward CF.

The Only thing I can think of, that CF formula =$S5>0.5
Did you Start selecting your CF Cells at Row 5 ?
If you had started the cell selection at a different Row (Not Row 5), your CF will All be Offset.

 

[excelnoob94]

Hi,

That's pretty simple and straight forward CF.

The Only thing I can think of, that CF formula =$S5>0.5
Did you Start selecting your CF Cells at Row 5 ?
If you had started the cell selection at a different Row (Not Row 5), your CF will All be Offset.

Thank you jtakw, your help is appreciated and it seems to have helped me solve the issue!

Would you mind if I ask another couple of questions about my excel sheet that I would appreciate assistance with?

Cheers,
Dean

 

[jtakw]

You're welcome, thanks for the feedback.

Sure, I'll be more than happy to help, if I'm able, but if your additional questions are not related to this CF issue, you're probably better off starting a new thread.
You'll probably get more responses from additional members who are willing to help.
Otherwise, continue your questions here.

Don't forget: Mark as Solution

 

[excelnoob94]

You're welcome, thanks for the feedback.

Sure, I'll be more than happy to help, if I'm able, but if your additional questions are not related to this CF issue, you're probably better off starting a new thread.
You'll probably get more responses from additional members who are willing to help.
Otherwise, continue your questions here.

Don't forget: Mark as Solution

Thank you.

I will ask you here and if unable to assist I will open a new thread

Firstly, I have a column of cells that are 'number' cells but display the text #VALUE! as this cell contains a formula which is unable to correctly work due to an incomplete cell elsewhere. This is fine, however, I was wondering if it is possible to make the cell say a number instead of #VALUE! i.e. if N5 has "6" the incomplete formula says "#VALUE!" in cell S5 but I want cell S5 to say "0".

Secondly, if I am unable to do the above, is it possible to put conditional formatting similar to the original question where if cell S5 says "#VALUE!" then all of row 5 turns purple.

Appreciate the help, hope it makes sense!

 

[jtakw]

If you can post your formula that's producing the #VALUE! error, and explain what it's supposed to do, may be we can fix the formula, as you say, "unable to correctly work due to an incomplete cell elsewhere"

Otherwise, to just Ignore the Error, you can wrap the formula with IFERROR:

=IFERROR(your formula here,0)

This will result 0 (zero) if your formula result is an Error.

As I say, if you post and explain your formula, may be we can try to fix it...

 

[excelnoob94]

If you can post your formula that's producing the #VALUE! error, and explain what it's supposed to do, may be we can fix the formula, as you say, "unable to correctly work due to an incomplete cell elsewhere"

Otherwise, to just Ignore the Error, you can wrap the formula with IFERROR:

=IFERROR(your formula here,0)

This will result 0 (zero) if your formula result is an Error.

As I say, if you post and explain your formula, may be we can try to fix it...

So the formula is "=O976/M976" very simple however, sometimes with my data I do not have a value in the "O" cell. So as a result the cell with the formula reads #VALUE!

hope this helps!

 

[excelnoob94]

So the formula is "=O976/M976" very simple however, sometimes with my data I do not have a value in the "O" cell. So as a result the cell with the formula reads #VALUE!

hope this helps!

If you can post your formula that's producing the #VALUE! error, and explain what it's supposed to do, may be we can fix the formula, as you say, "unable to correctly work due to an incomplete cell elsewhere"

Otherwise, to just Ignore the Error, you can wrap the formula with IFERROR:

=IFERROR(your formula here,0)

This will result 0 (zero) if your formula result is an Error.

As I say, if you post and explain your formula, may be we can try to fix it...

 

[jtakw]

Firstly, I have a column of cells that are 'number' cells but display the text #VALUE! as this cell contains a formula which is unable to correctly work due to an incomplete cell elsewhere. This is fine, however, I was wondering if it is possible to make the cell say a number instead of #VALUE! i.e. if N5 has "6" the incomplete formula says "#VALUE!" in cell S5 but I want cell S5 to say "0".

Secondly, if I am unable to do the above, is it possible to put conditional formatting similar to the original question where if cell S5 says "#VALUE!" then all of row 5 turns purple.

So the formula is "=O976/M976" very simple however, sometimes with my data I do not have a value in the "O" cell. So as a result the cell with the formula reads #VALUE!

2 ways to do this:

As I suggested in Post #6:

=IFERROR(O976/M976,0)

Or

If you Only want to "trap" O for no value:

=IF(O976="",0,O976/M976)

If you want to check if 1 Or both O & M has no value:

=IF(OR(O976="",M976=""),0,O976/M976)