Hi, I'm posting some data downloaded from google finance.

Column A contains prices
Column B contains the frequency of Column A in the data set (i.e. B1 contains the number of occurrences of A1). So 9 appears 4 times.
Column C is simply numbered
====================
Column A goes stale at $6 from (A248:A500).

I want to locate this stale period and change all values to 100 and color them pink. The problem is, if the price was 6 outside this stale range, I do not want it changed.

i.e. I do not want A177 changed. I do not want anything except A248:A500 changed for that matter.

Thanks,
nick



Here is what I have so far:

1) I filter A for my own sanity
2) I filter B in descending order to bring the most frequent price to the top
3) I simply change all the 6's to 100 and color them pink
4) Finally, I filter back to the original order. 

Sub aa()
Dim x As Double
Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Order1:=xlAscending
Range("A1").CurrentRegion.Sort Key1:=Range("B1"), Order1:=xlDescending
    With Range("A1:A" & Cells(1, 2))
        .Value = 100
        .Interior.ColorIndex = 40
    End With
Range("a1").CurrentRegion.Sort Key1:=Range("C1"), Order1:=xlAscending
End Sub
Test2.xlsm