Colouring cells in column by smallest value

[teamsimpson]

Does anyone have a piece of code that will
1)colour a cell in a column which has the smallest value (numerical), i need to apply this to columns c,d,e and f
2)colour a cell in a column which has the largest value (numerical),i need to apply this to columns g and h.
Thanks

 

[Andrew Poulsom]

Assuming the data is in rows 1 to 5:

1. Select range C1:F5 and choose Format, Conditional Formatting. Formula Is:

=C1=MIN(C$1:C$5)

choose your Format and click OK.

2. Select range G1:H5 and choose Format, Conditional Formatting. Formula Is:

=G1=MAX(G$1:G$5)

choose your Format and click OK.

 

[teamsimpson]

Sorry Andrew i should have said, i needed the code for a macro i am building.

 

[Andrew Poulsom]

Well can't you record your actions following my suggestion then use that in your code? Or is there a reason you don't want to use conditional formatting?

 

[teamsimpson]

Sorry i did'nt realise that you could record that sort of thing, including formula etc.

 

[teamsimpson]

The conditional format works fine, how would the formula look if the min ar max values were times, ie. 2'54"76, or 51"28
This message was edited by teamsimpson on 2002-10-14 12:12

 

[Andrew Poulsom]

It should work provided that the time is decimal formatted as hh:mm:ss. Is that the case?

 

[teamsimpson]

no unfortunatley the worksheet if from another file, it is input into my file as an embedded object, the some column values are decimal numbers and other columns are times for example, 2'54"76 which is 2minutes and 54.76 seconds