Conditional formatting under condition of min excluding blanks

[kip894]

Hello all, hope that you can share your wise suggestion to my problem.
In each row of row 4 to 7 in my attachment, I try to find out the cell in column B to G which is not blank and is the minimum among all non-blank cells not less than the input value (i.e. value in column A) and fill in color.
For example, in row 4, C4 should be colored;
in row 6, only B6 should be colored (and those blank cells in row 6 should NOT be colored).

I tried different kinds of formulae in conditional formatting but none of them can work in all cases in the attachment (for example, after selecting range as B4:G7, I tried
= $B4 = MIN(IF($B4:$G4>=$A4, IF(ISNUMBER($B4:$G4), $B4:$G4))) )

Please help to find ways for such purpose.
Using conditional formatting or VBA are both OK for me.

PS: Attached is an excel file from Office 2000, and similar purpose has to be applied on another one from Office 2007. Please state any difference when deploying your suggestions to these two versions.

[ramananhrm]

Please try this function in cond. formatting.
=AND(MIN($B4:$G4)=B4,B4<>"")

[kip894]

Please try this function in cond. formatting.
=AND(MIN($B4:$G4)=B4,B4<>"")

Thank you for your suggestion.
However in the minimum function, I have to consider only those non-empty cells which are not less than the input value in column A.

[6StringJazzer]

Here is a solution that uses a VBA user-defined function. The other way I was able to find requires an array formula, which can't be used in a conditional formatting formula. Another way to do it is to use a "helper" column using that array formula. The posted solution requires no helper column.

There is one column that shows how the function works but it is not required for the solution, it's just a demo.

The function works in 2003 forward. I composed the conditional formatting in 2010, which is the same as 2007, and should also port to 2003 (which I don't have, so I can't test it).