• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

I need a Formula for Conditional Formatting

Dear All,

I need a conditional formula for the following problem.

In a Range B5:T500 , I need a conditional formula that highlights min of ( d6,H6,L6,P6,T6) similarly for other rows for above condition.

I am doing for individual rows, please assit me .

Thank you.




obviously it cannot highlight B5, but it won't be a problem to add the C F to too many cells.


Excel Ninja
Staff member

When you say you want to highlight the minimum of D6,H6,L6,P6,T6 in B5:T500

do you mean you want to highlight the actual cell in each Row from D6,H6,L6,P6,T6 which ever is minimum, and ignore the cells in between ie: E6:G6, I6:K6 etc
Dear Hui,

I want to highlight minimum of each row from D6,H6,L6,P6,T6 , The range is around 700 rows, insted of formatting each row, i want to format till the required range.


Excel Ninja
Staff member
This had me going in circle for a while, but give the following a try:

Select the range B5:T500

Clear all existing conditional formats for the area B5:T500

Add 2 CF's

CF1 =MOD(COLUMN(),4)<>0 applies to:$B5:$T500 No Format, Stop if True

CF2 Bottom 1 applies to:$B5:$T500, set your highlight format
Dear Hui,

I tried but it was not working, I don't know where i have done wrong can i forward the file to you? Plase share your email id .



Excel Ninja
Staff member

I give up, I can't get it to work on your data and it is patchy on my simpler test data

However another idea:

What about shifting all the supply rates Columns to the end ie: after Col AE so there is no gaps between them, instead of having them spread all through the data

and then just do a minimum of that range AE-AK with Conditional Formatting


Excel Ninja
Staff member

XLD's formula with a bit of modification does work

You will need to use 2 Conditional Formats

CF1: Formula =MOD(ROW(),2)=0 No Format, Applies to I16:AF52, Stop if True = Ticked

CF2: Formula =I16=MIN($I16,$M16,$Q16,$U16,$Y16,$AC16,$AF16) Setup your format, Applies to I16:AF52

You will need to ensure that your rows which have values are even rows for CF1 to work, so insert Rows at the top of each section of furniture etc to fix that up.

Thanx XLD