# Conditional Formatting Colours Wrong Cell

#### excelnoob94

##### New Member
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

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### jtakw

##### Well-known Member
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

##### New Member
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

##### Well-known Member
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.

Don't forget: Mark as Solution

#### excelnoob94

##### New Member

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.

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

##### Well-known Member
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:

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

##### New Member

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:

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

##### New Member
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:

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

##### Well-known Member
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:

Excel Formula:
``=IFERROR(O976/M976,0)``

Or

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

Excel Formula:
``=IF(O976="",0,O976/M976)``

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

Excel Formula:
``=IF(OR(O976="",M976=""),0,O976/M976)``

Replies
3
Views
107
Replies
0
Views
50
Replies
1
Views
89
Replies
5
Views
53
Replies
4
Views
152

1,130,337
Messages
5,641,571
Members
417,223
Latest member
jelena_

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back