# Conditional Formatting question

#### MikeyZ

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

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### AliGW

##### Banned
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")

Last edited:

#### MikeyZ

##### Well-known Member
not sure what a CF rule is, or I do and it isn't registering

#### MikeyZ

##### Well-known Member
scratch that last comment.

Here's my rule:

=F14<>G14

#### AliGW

##### Banned
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?

Last edited:

#### MikeyZ

##### Well-known Member
worked great thanx

You're welcome!

#### MikeyZ

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

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

Replies
0
Views
146
Replies
0
Views
287
Replies
2
Views
361
Replies
3
Views
484
Replies
12
Views
363

1,195,667
Messages
6,011,053
Members
441,580
Latest member
BornholmerBjarne

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