FORMULA not working with conditional format

[Danwatkins1999]

I am using an IF statement to copy data from another cell, but I cannot get the conditional formatting to work on the cell containing the formula? I think this is because the conditional formatting is picking up the formula instead of actual value it displaying. Is there a way to automatically get get the formula to paste or copy this value as a true string so that the conditional formatting works ? 

[#2]

@Danwatkins1999 '

Conditional formatting acts on a cell's value, not on its formula.

Could you attach a small sample workbook demonstrating the problem, and explain what the conditional formatting rule should do?

[#3]

hi Hans
the conditional formatting is between two cell values to determine if the entered results are within limit or not, any rows marked x are to be manually entered results, this works fine. but it is when the data is copied on the rows that are not marked that doesn't work with conditional formatting.

[#4]

@Danwatkins1999 

Thanks! The problem is that the values in B16:B32 are text, so the mess up the numerical comparison when used in the formulas in M16:P32.

You can correct this as follows:

• Select B16:B32.
• Change the number format from Text to General.
• On the Data tab of the ribbon, click Text to Columns, then click Finish without changing any settings.

[#5]

@Hans Vogelaar 

the report has data imported from another software so row B automatically turns back into text format, when changing text to columns it removes the decimal places in column B, the decimal places need to remain the same. its very close to what I am after ! is there any other ways to do this so it automatic and the decimal places are not changed? 

I have attached the blank template and the exported results prior to what you have said below. it doesn't matter that not all of the values exported are not be in-between column G and H. if you could take another look at this it would be much appreciated. 

note - it is the QA tap in reference to our subject / topic 

[#6]

@Danwatkins1999 

I'm totally confused now.

In your first sample workbook, column B contained single values.

In your "exported data" workbook, however, column B contains a mixture of text values, single values that look like numbers, and two numbers separated by /.

So a "between" comparison makes no sense

[#7]

sorry, I should have explained it better ! the idea is to save time on manual entry of results, i want to have column B to be automatically filled in the results area. it will only be the single values this will work on.  it doesn't matter if the results copied has text or two numbers separated with "/"  as these will be manually typed over, as it isn't correct to the in-between value like you said. so please ignore these. it is only the single values EG rows: 24 - 26 I am interested in. I am not sure if i need a value function somewhere ?

[#8 · Accepted solution]

@Danwatkins1999 

Thanks for the clarification. See the attached version.

You had rules for most individual rows; I have reduced it to 4 rules for the entire range.

[#9]

That is perfect. thank you very much. very helpful