Conditional Formatting Problem

polkev

New Member
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?

KP

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

Replies
3
Views
110
Replies
0
Views
54
Replies
4
Views
66
Replies
7
Views
84
Replies
24
Views
169