I need a Formula for Conditional Formatting

vmohan1978

Member
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.

xld

Member
Try

=B5=MAX(D5,H5,L5,P5,T5)

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

Hui

Excel Ninja
Staff member
vmohan1978

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

vmohan1978

Member
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.

Hui

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

CF1 =MOD(COLUMN(),4)&#60;&#62;0 applies to:\$B5:\$T500 No Format, Stop if True

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

vmohan1978

Member
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 .

Thanks

Hui

Excel Ninja
Staff member
Ihuitson at gmail dot com

Hui

Excel Ninja
Staff member
Mohan

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

xld

Member
Doesn't this do it

=D5=MIN(\$D5,\$H5,\$L5,\$P5,\$T5)

Hui

Excel Ninja
Staff member
Mohan

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