Conditional Formating based on a different column

[jimboryan]

I don't know why but I can't make my conditional formatting work right. In column G I have a formula that produces a number. I want to compare that to the number in column B (same row) and if G is larger, fill the cell with blue. I've tried two or three different ways and can't get it to work. What is the correct syntax?

Thank you,

[judgeh59]

does this help?

[jimboryan]

I hadn't see that option. I tried it but it didn't work. I've checked that the two cells in question are formatted as numbers, so I don't know what they heck I'm doing wrong. I've attached a sample. G5 should be highlighted, and G12 should not. The rest of G doesn't need the formatting rule.

[judgeh59]

A note about formatting.....formatting never changes the actual data, only how it's presented...the issue was the data in Col B is text. A little trick I do sometimes is multiple my "number" by 1 and then XL forces it to be a real number, not a text number...check out the file...

[judgeh59]

did this get updated?....I think the website burped when I went to send this....

[FlameRetired]

A note about formatting.....formatting never changes the actual data, only how it's presented...the issue was the data in Col B is text. A little trick I do sometimes is multiple my "number" by 1 and then XL forces it to be a real number, not a text number...check out the file...

What judgeh59 says works, only I put a double unary in front of $B$5 like this =--$B$5.
Your formatting kicked right in.

judgeh59 applied this directly to your spreadsheet formula. I applied mine in the conditional formatting formula. Either way works.

[FlameRetired]

did this get updated?....I think the website burped when I went to send this....

Downloaded fine for me.

[judgeh59]

@FlameRetired......thanks...I wasn't even sure the post actually made it.....thanks again....

I like the idea of the "--"....but the other side is if the data is actually supposed to be a number then the "--" in the CF doesn't change the data....

like you said though - potato, potaatoe, tater...

[jimboryan]

only I put a double unary in front of $B$5 like this =--$B$5.

Not sure what this means/does

[FlameRetired]

@FlameRetired......thanks...I wasn't even sure the post actually made it.....thanks again....

I like the idea of the "--"....but the other side is if the data is actually supposed to be a number then the "--" in the CF doesn't change the data....

like you said though - potato, potaatoe, tater...

Good point. Wasn't sure what OP was after. Just in case they wanted the 4 to remain as text (probably not likely) I thought it was worth including. Thanks, though.

[FlameRetired]

Not sure what this means/does

The double unary does the same thing as *1 or +0 or /1 or ^1. It coerces numbers that are text into their numeric value. I use -- because it's faster to type and I'm lazy. I figured since it works in sp'sheet formula it probably would work in CF formula. Used in CF it leaves the text "value" in B5 unmolested while instructing G5 to turn blue.

If you want what's in B5 to evaluate as a numeric value go with what judgeh59 suggests. In CF =--$B$5 merely 'tells' G5 to treat B5 as if it were an actual number with value of 4.

It depends on what you are wanting overall. In either event G5 turns blue.

Does this help?

[jimboryan]

Thanks for the explanation, and the solution seems to work. Thanks twice!

[FlameRetired]

Thanks for the explanation, and the solution seems to work. Thanks twice!

You're welcome.