how to highlight outliers

[missy22]

I would like to request some help, in creating macro, which can find and highlight outliers based on the calculation logic below.

I have dataset in one sheet

calculation logic:
if name's price (num1 -- Column B) are under high & low Range1's values (Column I & J), then leave cells blank, otherwise, if they are not between the range1's values (high and low), then highlight the prices in red.

if name's price (num2 -- Column C) are under high & low Range2's values (Column I & J), then leave cells blank, otherwise, if they are not between the range2's values (high and low), then highlight the prices in yellow.

I have attached a sample dataset with desired output highlighted.
out_num.xlsm

Please note, the actual dataset is more 5,000 lines of data records.

Any help would be very much appreciated. Thanks.

[stnkynts]

Your sample dataset is a contradiction to your specified "calculation logic". See cell C5, C6, C9, and C12 which is under (C9 is over) high & low Range2's values, but is highlighted yellow. Please make sure the information you submit is accurate and provide updated instruction on the parameters of your workbook.

[missy22]

apology for the error and for not making my explanation clearer. Thanks for your feedback.

if the num's values are between the ranges then leave the cells blanks, otherwise if nums are not under and are over the ranges value, then highlight the outliers.

Hence, C5, C6 and C9 cells, are correctly highlighted as the nums are over the Range2 values. I have attached updated example file below, for further detail in the problem.
out_num2.xlsm

Please advise further. Many thanks.

[stnkynts]

Your post is contradictory again.

otherwise if nums are not under and are over the ranges value, then highlight the outliers.

I believe you do want to highlight if the numbers are under the range's value. Try this:

Please Login or Register  to view this content.