Conditional formatting issue

[chriscavsib]

Hi all
We have a spreadsheet which has columns of data extracted from other sheets via formula.

What we want to do is have a conditional format on one column if the cell value is higher than the cell value in the other column.

Currently, when we've tried it, the formula is picking up various cells.. Most of which aren't what we want.
We've tried changing the format of the cells in case they we're numbers but that isn't the issue.
We've tried things like =IFE3>F3 etc but can't seem to get it to function

Any ideas?

Thanks in advance

 

[Joe4]

Welcome to the Board!

Could you show us a small sample of your data and expected results?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[chriscavsib]

Hi
Unfortunately, using a work laptop I'm restricted in what I can download etc. I can send screenshots of the cells in question?

 

[Joe4]

Yes, you can copy/paste images to your post.

 

[Joe4]

By the way, if you just wanted to check to see if E3 is greater than F3 in Conditional Formatting, your Conditional Formatting formula should look like this:

=$E3>$F3

 

[chriscavsib]

We want the values in BQ to show in Red if they are greater than the values in BM.
There are formulas behind both BQ and BM

This is the current conditional format on line 18 as the value in BQ is higher than BM

 

[Joe4]

No, do NOT use the Cell Value option.
Use the "Use a formula to determine which cells to format" option, and use the formula I gave you, which in your case looks like it should be:

=$BQ18>$BM18

If you want to apply it to a whole bunch of cells at once, do NOT lock the row portion of the formula.
Just select all the cells you want to apply it to first, then set up the CF rule, and write the formula as it applies to the very FIRST cell in the range you selected, and it will adjust the formula for the rest of the rows (which is why we do not want to lock the row portion of the range references in our formula).

 

[chriscavsib]

Hi
I tried that but this was the result

I've tried formatting the cells differently and have checked if the cell is showing the figures as numbers (which it is)

 

[Joe4]

It is possible you may have some fractional amounts not showing.
Try this variation of the formula:

=ROUND($BQ18,0)>ROUND($BM18,0)

 

[chriscavsib]

That seems to have done it. Thank you very much