How to Display Conditional Formatting of One Cell Based on Another

dmingle

New Member
Joined
Oct 16, 2011
Messages
8
Objective: to display conditional formatting of one cell based on another
Conditions:
  • If the cell is blank = white
  • if the cell has a value less than variable X = green
  • if the cell has a value equal to or greater than 1.24 of * X = yellow
  • If the cell value is greater than 1.25*X = red.
  • {X is the benchmark value}

Problem:
All conditional formatting works as it should, however when the value is blank it defaults to the red color. It should be white.

Here is the formula that I have in the conditioned cell:

Code:
=IFNA(ROUND(HLOOKUP(A16,Login!$C$1:$ZZ$1000,2,FALSE),2),"")

If you take the IfNA() function out of the above formula the result would be "#N/A". This formula recognizes the #NA result and transforms it to "".

I appreciate any help.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Objective: to display conditional formatting of one cell based on another
Conditions:
  • If the cell is blank = white
  • if the cell has a value less than variable X = green
  • if the cell has a value equal to or greater than 1.24 of * X = yellow
  • If the cell value is greater than 1.25*X = red.
  • {X is the benchmark value}

Problem:
All conditional formatting works as it should, however when the value is blank it defaults to the red color. It should be white.

Here is the formula that I have in the conditioned cell:

Code:
=IFNA(ROUND(HLOOKUP(A16,Login!$C$1:$ZZ$1000,2,FALSE),2),"")

If you take the IfNA() function out of the above formula the result would be "#N/A". This formula recognizes the #NA result and transforms it to "".

I appreciate any help.

On the conditonal format dialogue box put that blank cell condition on top...
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top