• 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

    Hui...

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

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

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 .

Thanks
 

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
 

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
 
Top