Conditional Formatting question

[MikeyZ]

I have this fx in col "G".
=IFERROR(VLOOKUP(E9,'[QtyOnHand'!$A:$B,2,),"0")

It just pulls the on hand inv qty over from another sheet so I can compare it to yesterdays on hand qty I posted in F.

When qty differ between F and G I want to have either F or G change color. I picked red.
Problem is, if I have a 0 in F, the true value in G is #N/A and not 0. therefore I get a lot of "red" popping up. So I'm not getting a true picture.

Is there an easier way to achieve my goal?

 

[AliGW]

How have you set up the CF rule? Can you explain what exactly you mean by this:

When qty differ between F and G I want to have either F or G change color.

Why not use this in F?

=IFERROR(VLOOKUP(E9,'[QtyOnHand'!$A:$B,2,),"No Match")

 

[MikeyZ]

not sure what a CF rule is, or I do and it isn't registering

 

[MikeyZ]

scratch that last comment.

 

[MikeyZ]

Here's my rule:

=F14<>G14

 

[AliGW]

OK. Use this as your formula in the cell:

=IFERROR(VLOOKUP(E9,'[QtyOnHand'!$A:$B,2,),"")

and this as your CF rule:

=IF(OR(F9="",G9=""),FALSE,F9<>G9)

PS Are you sure it's E9 in the VLOOKUP?

 

[MikeyZ]

worked great thanx

 

[AliGW]

You're welcome!

 

[MikeyZ]

Back again.
I jumped the gun when I said this worked.
It works fine when col "G" is populated with numbers.
Then when F differs from G the cell turns the color I want.

When the qty in F = anything > 0, and the value in G = "", CF fails to work.

=IF(OR(F9="",G9=""),FALSE,F9<>G9)

Any further suggestions?

 

[MikeyZ]

just to clarify.

The values in F are the previous inventory qty's.
The values in G are the current live values based on the system live report.
I want the cell to turn the color I selected when the F value differs from the G value.
When G gets a #N/A value or "", CF doesn't work.