Conditional Formatting Help!

[mgiglab]

Hi there,

I would like to use the conditional formatting highlighting feature to analyze and compare pricing between multiple vendors for the same product, following these rules:

1.) Light red background with dark red text for cells containing comparative vendor prices that are greater than the current price

2.) Light green background with dark green text for cells containing comparative vendor prices that are less that the current price

3.) No action to those cells that are blank

I have been running into some issues doing this on my own and would appreciate some assistance in explaining how to go about applying these rules to over 400 lines of inventory. I've included a mock sample of the spreadsheet to help explain the format I'm dealing with. 

Some of the questions I have are:

- How do I stop these rules from applying to blank cells?

- How do I apply these rules across multiple rows, only comparing the costs in columns G-I to the current cost in column E for that same row each time? For example, for Catalog # 2222--the costs from Vendor One (26.00) and Vendor Two (23.00) are only being compared to the Current Unit Cost (25.00)

Please let me know if any additional clarification is needed to provide some solutions. 

Cheers,

[#2]

@mgiglab -

If this is your goal:

Use these two Conditional Formatting Rules:

=$E2<G2

=ISNUMBER($E2)>NOT(ISNUMBER(G2))

[#3]

@ChrisMendoza 

Thanks for responding. That is my goal; however, when I input those rules I do not achieve the same results as displayed in your comment. Do you have any other tips, perhaps having to do with how you actually entered the information?

Thank you,

[#4]

@mgiglab - The attached file did not work for you? I did have to create rules from formula and assign fill and font colors.

I attached a short gif file showing how I accomplished.

[#5]

@ChrisMendoza Looks like I was missing that last step--switching the order of the rules. Thank you so much for the wonderful tips!