Conditional formatting for cells depending on other cells ranges Solved/Closed

Question

First off, I am not even sure if i worded my question correctly, so sorry if i have.
What i want to do is... have for cells b4:b7 to have on green cell for the highest value from h4:h7.  I can do it singularly, but id like to know if there is some function im missing because this could buy me a lot of time.

For example is there a function to apply whatever cell is selected when copying conditional formatting? Or something along the lines of 

If "greatest" h4:h7 then "format green"

Or can i do a condition from a condition?  How would i put that in a problem?

Again I'm sorry if this is confusing, and im sure i can better word this, im just slightly frustrated.

venkat1926 · Answer

I am rephrasing sit see whether it tells what you want

i have some entries (numbers) in B4 to BV7
I have some number H4 to H7 also 

if any cells in B4 to B7 has got the maximum of H4:H7 it shd be colored green

if so try this macro 

Sub test()Dim r As Range, c As Range, mmax As DoubleSet r = Range("B4:B7")mmax = WorksheetFunction.Max(Range("H4:H7"))r.Cells.Interior.ColorIndex = xlNoneFor Each c In rIf c = mmax Then c.Interior.ColorIndex = 4Next cEnd Sub

TrowaD · Answer

Hi Derrall,

Or use this formula in conditional formatting in cell B4:
=MAX($H$4:$H$7)=H4 

Now drag the formula down applying format only.

Best regards,
Trowa

Derrall · Answer

This is 100% what i needed thank you TrowaD ^_^

And venkat1926 thank you for your assistance too!

Again sorry if my question was asked incorrectly, in the end i got exactly what i needed!

Conditional formatting for cells depending on other cells ranges

3 responses

Similar discussions

Subscribe To Our Newsletter!