Conditional Formatting Problem

polkev

New Member
Joined
Oct 4, 2006
Messages
9
Hello,

I have created a simple three (3) page work book; one page is data, one is a report of activity report for the current day and the previous 6 and the other is a report of activity for the current day and the 29 previous days. I am using the following formula to lookup and populate the reports: =IF(COUNTIF(Date,A8)=0,"",VLOOKUP($A8,Data!A:D,2)). The report populates as expected. I want to highlight all the data in column ‘C’ of the report pages that are over 80. I set up the conditional formatting to do this, but it is also highlighting cells with a null value. I can’t use a 0 in the place of a null value because an average function is used on each report. Can I insert an argument to prevent the null cells from being highlighted? I have tried adding a second condition; e.g. ‘less then 1,’ or, ‘equal to “”,’ but neither of these arguments have worked. Can someone help?

Thanks in advance,

KP
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
I believe I could help you, but, I would ask you to post your present conditional format formula. Also, what is "a cell with a null value"? Do you mean a cell with nothing in it? If so, I believe this is called "a blank cell". Once you post answers to the above, I will be ahppy to help you.
 

polkev

New Member
Joined
Oct 4, 2006
Messages
9
The conditional formatting reads, “Cell value is greater than 80.” The call contains the function, “=IF(COUNTIF(Date,$A9)=0,"",VLOOKUP(Data,$A9,2)).” The result of the IF function is true, “”. I would expect there to be no conditional formatting but there is.
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
Assuming your answer is in cell B4, then, for your conditional formatting, use Formula Is: =CODE(B4)>80. Let us know if this fixes your problem.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,674
Messages
5,549,358
Members
410,910
Latest member
DessertDiva
Top