CF in a filter

C

craigproudfoot

I have a list of prices in a list (column F) which has a CF attached t
the whole column to highlight the lowest number in blue.
When i apply a filter to column B and reduce the overall list o
numbers in column F, the conditional format disappears or rather, i
does not highlight the lowest number in the new reduced list.
Example: say the lowest number in the unfiltered (column F) list i
2.5
The number 2.5 becomes highlighted in blue
When i apply a filter to column B, the lowest number in the resultin
list (column F)is not highlighted in blue (unless of course th
resulting list still contains the number 2.5)
i'm sure the above is incomrehensible - my apologies
 
B

bpeltzer

What's the condition you've established to identify the minimum? If you're
comparing the particular value to something like subtotal(5,range), then
you'll have issues, as the subtotal functions honor the filter. If that's
it, just change to min(range). If not, please post the condition you're
using in the CF.
 
C

craigproudfoot

i'm using cell value is equal to =MIN($F$3:$F$2000)
it doesn't acknowledge the filte
 
R

Rowan Drummond

Try changing your CF so that Cell Value is equal to =SUBTOTAL(5,$F$2:$F$20)

Hope this helps
Rowan
 
C

craigproudfoot

Rowan

Thanks, that worked - it now higlights the lowest number in the list of
column F, even when a filter is applied.
It only applies the formatting to the particular cell with the lowest
value - How can i make the formatting cover the full row? My formula is
as follows:

cell value is > equal to > =SUBTOTAL(5,$F:$F)

cheers
 
R

Rowan Drummond

Delete your current conditional formatting. Then select the entire range
excluding the headings making sure that A2 is the activecell. A2
should be white and you should see A2 in the Name Box on the formula bar.

Then apply your conditional formating making the condition FORMULA IS:
=A2=SUBTOTAL(5,$F:$F)

Hope this helps
Rowan
 
C

craigproudfoot

It still only highlights the single cell - what relevance is A2 i'm no
sure i understand that one
 
R

Rowan Drummond

Apologies that was my mistake. The formula should have read:
=$F2=SUBTOTAL(5,$F:$F)
Apply this formula as instructed before with all the data selected and
A2 as the activecell.

Regards
Rowan
 
Top