Selecting rows on a sorted file based on a value in the top most row

[MarkHSal]

Hi, Probably not the best thread title in the world but my problem is that I have a sorted excel file that I have matched using a fuzzy logic UDF on a cell which produces a % match to the same column cell below. I.e. cell C2 = C3 produces, for example, a % match of 76%.

My problem/challenge is to highlight both rows based upon a threshold % of, lets say, 60% and above of the first (upper) record. Please see the loaded file for some example data.

Thanks in anticipation Mark

 

[NARAYANK991]

Hi Mark ,

Something like this ?

Narayan

 

[MarkHSal]

Hi Mark ,

Something like this ?

Narayan

Thanks for this and for coming back so quickly. Yes, this is exactly what I'm looking for. Could you explain how you did this? I'm sure it was something to do with Conditional Formatting, but I couldn't get it to work.

Thanks

Mark

 

[NARAYANK991]

Hi Mark ,

You are right ; there are just 2 rules to be applied after selecting the range A2 through D699 :

=$D2>=Threshold

=AND(ISNUMBER($D1),$D1>=Threshold)

The first rule will highlight the row which has the percentage value greater than or equal to the threshold value which I have put to 60 %.

The second rule will highlight the current row if the row above it has the percentage value >= the threshold. I have included the additional check because without it , the first row is highlighted because the header cell which contains text is always greater than a numeric value.

Narayan

 

[MarkHSal]

Hi Mark ,

You are right ; there are just 2 rules to be applied after selecting the range A2 through D699 :

=$D2>=Threshold

=AND(ISNUMBER($D1),$D1>=Threshold)

The first rule will highlight the row which has the percentage value greater than or equal to the threshold value which I have put to 60 %.

The second rule will highlight the current row if the row above it has the percentage value >= the threshold. I have included the additional check because without it , the first row is highlighted because the header cell which contains text is always greater than a numeric value.

Narayan

Many thanks for this Narayan - so simple and elegant - just my fuzzy brain needed a bit of help.

Mark