Office 2007 Conditional Formatting

[robert morris]

I have the following formula which highlighs the lowest five of ten numbers.

=ISNUMBER(MATCH(H10,SMALL($H10:$Z10,COLUMN($A:$E)),0))

All works well until I Save and exit. When I re-open the worksheet the
highlighting is gone.

I can then go to CD - Manage Rules - Edit Rule - Apply and OK. At this
point, all formatting reappears.

This happens on all worksheets.

Any ideas?

Bob

 

[Bernard Liengme]

Why not use the "Format only Top or Bottom Ranked" feature of Excel 2007
This is easier than messing with an array formula (as yours is) in CD
best wishes

 

[robert morris]

Bernard;

Thanks for the answer. Possibly I did not completely explain what I do with
my formula in CD. I can have up to 200 Rows and each Row MUST stand on it's
own. I have tried using the "Format only Top/Bottom Ranked. It works for
one Row but I've had no success past the one Row. Maybe I'm not entering the
Range properly. My Range in this case would be H10:Z200.

Bob

 

[Bernard Liengme]

If you select all the row, then the condition will apply to the multi-row
range: it will highlight the lowest 5 in the entire range,

I entered some numbers in H10:Z15
I selected the first range (H10:Z10) and used CD to highlight the Bottom 5
Then I selected H10:Z10, clicked on the Format Painter (paintbrush icon to
the left in the Home tab), and 'painted' the other 4 rows.
This was successful in marked CD highlight the bottom 5 cell in each row
independently

Meanwhile, I am playing with your formula to see if there is a bug in Excel
2007.

Alternative to my suggestion above: experiment with this
Select all 200 rows, I will assume H10 is the first cell in this range
For the CD formula use: =H10<SMALL($H10:$Z10,6)
Note where the $ symbols go.
This worked well for me

best wishes

 

[robert morris]

Bernard;

I've tried both of your suggestions. Both "retain" the highlighting after
Save & Close.
That is the good news. The bad news; Solution #1 using "Top/Bottom" works
in the first Row (Row 10). Used Format Painter for Rows 11:138 (this
particular WS uses 138 Rows) which only highlighted the lowest ONE of all the
scores in the Range H11:Z138.

Solution #2 works very well except, again using the Format Painter, only
paints through Row 100. I've done this one several times with the same
result. I would use this one if I could get it to work past Row 100.

If you would consider looking at my WS, I could send it VIA email.

Bob

 

[Bernard Liengme]

Yes, do send me a file
Get my email from my website, please

 

[robert morris]

Bernard,

Did you receive my email?

Bob

Yes, do send me a file
Get my email from my website, please
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

 

[Bernard Liengme]

Not yet

 

[robert morris]

Bernard,

Have you received my two WS's? I answered your reply yesterday.

Bob Morris

 

[Bernard Liengme]

You must have got my reply by now

 

[robert morris]

Yes I did. I sent you my thoughts last evening.

Bob