Conditional Formatting Colours Wrong Cell

excelnoob94

New Member
Joined
Mar 31, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

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

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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.
 
Solution

excelnoob94

New Member
Joined
Mar 31, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Mar 31, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Mar 31, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Mar 31, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top